Complex IF formula

fazthfc

New Member
Joined
May 6, 2015
Messages
4
I'm struggling with a complex IF formula, hoping someone can help.

In D2 I have a category number 1-4.

In F2 I have a number which could be positive or negative.

I need a formula in G2 to work out the following conditions:

IF F2 is 0, G2 = 0

IF D2 = 1 AND F2 is GREATER than zero MULTIPLY F2 by -0.1.

IF D2 = 1 AND F2 is LESS than zero, G2 = 0.4

IF D2 = 2 AND F2 is GREATER than zero MULTIPLY F2 by -0.1.

IF D2 = 2 AND F2 is LESS than zero, G2 = 0.5

IF D2 = 3 AND F2 is GREATER than zero MULTIPLY F2 by -0.2.

IF D2 = 3 AND F2 is LESS than zero, G2 = 0.6

IF D2 = 3 AND F2 = -1, G2 = 0

IF D2 = 4 AND F2 is GREATER than zero MULTIPLY F2 by -0.3.

IF D2 = 4 AND F2 is LESS than zero, G2 = 0.7

IF D2 = 4 AND F2 = -1 OR -2, G2 = 0

I have a formula which takes into all of the above but I am struggling with adding the part where F2 3 or 4 equal -1 or -1 or -2. I am only getting errors however I do it. Would appreciate any support.

=IF(F2=0,0,IF(AND(F2>0,(D2=4)),F2*-0.3,IF(AND(F2>0,(D2=3)),F2*-0.2,IF(AND(F2>0,(D2=2)),F2*-0.1,IF(AND(F2>0,(D2=1)),F2*-0.1,IF(AND(F2<0,(D2=4)),+0.7,IF(AND(F2<0,(D2=3)),+0.6,IF(AND(F2<0,(D2=2)),+0.5,IF(AND(F2<0,(D2=1)),+0.4,)))))))))
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi,

Check if this works for you:

Book1
DEFG
24-20
Sheet1
Cell Formulas
RangeFormula
G2=IF(OR(F2=0,AND(OR(D2=3,D2=4),OR(F2=-2,F2=-1))),0,IF(F2<0,CHOOSE(D2,0.4,0.5,0.6,0.7),F2*CHOOSE(D2,-0.1,-0.1,-0.2,-0.3)))
 
Last edited:
Upvote 0
Try this Macro
Code:
Option Explicit
Sub Complex_Ifs()
Dim my_val
Select Case [d2].Value
 Case 0
  my_val = 0
  '==================
 Case 1
   Select Case [F2]
    Case Is >= 0: my_val = [F2] * -0.1
    Case Else: my_val = 0.4
   End Select
  '================
 Case 2
   Select Case [F2]
    Case Is >= 0: my_val = [F2] * -0.1
    Case Else: my_val = 0.5
   End Select
   '================
 Case 3
   Select Case [F2]
    Case Is >= 0: my_val = [F2] * -0.2
    Case Else: my_val = 0.6
   End Select
' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   ' and so On to the last condition........
' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   Case Else: my_val = "Bad Answer"
  End Select
   [G2] = my_val
End Sub
 
Last edited:
Upvote 0
@jorismoerings:
This part doesn't look correct

=IF(OR(F2=0,AND(OR(D2=3,D2=4),OR(F2=-2,F2=-1))),0

OP said
IF D2 = 3 AND F2 = -1, G2 = 0

If D2 =3 and F2 = -2 your formula will return 0, thats not what the OP said

It should be

IF(OR(F2=0,AND(D2=3,F2=-1),AND(D2=4,OR(F2=-1,F2=-2))),0
 
Last edited:
Upvote 0
Hey, try this:

Code:
CHOOSE(D2,CHOOSE(SIGN(F2)+2,0.4,0,F2*-0.1),CHOOSE(SIGN(F2)+2,0.5,0,F2*-0.1),CHOOSE(SIGN(F2)+2,IF(F2=-1,0,0.6),0,F2*-0.2),CHOOSE(SIGN(F2)+2,IF(OR(F2=-1,F2=-2),0,0.7),0,F2*-0.3))
@jorismoerings

I think your formula breaks at 1 point: D2 = 3 and F2 = -2, G2 should give 0.6 but yours gives 0, apart from that all good!
 
Upvote 0
@Special-K99 @tyija1995

Noticed it myself as well, but i used the OP's text
struggling with adding the part where F2 3 or 4 equal -1 or -1 or -2
Comparing list of criteria with text leaves an open interpretation but i think we got the OP on track by shortening his string of multiple IF's.
And by doing the change you suggested OP has 2 possible solution for every option of interpreting the list or the text.
 
Upvote 0
@Special-K99 @tyija1995

Noticed it myself as well, but i used the OP's text
Comparing list of criteria with text leaves an open interpretation but i think we got the OP on track by shortening his string of multiple IF's.
And by doing the change you suggested OP has 2 possible solution for every option of interpreting the list or the text.


Thanks all for your excellent responses and apologies for the ambiguity on OP. The formula provided by @tyija1995 works perfectly. I now need to explore 'choose' and 'sign' as I've not seen these before!

Thanks
 
Upvote 0
I show you another option:

Code:
[SIZE=2]=IF(OR(F2=0,AND(F2=-1,D2=3),AND(OR(F2=-1,F2=-2),D2=4)),0,IF(F2<0,LOOKUP(D2,{1,2,3,4},{0.4,0.5,0.6,0.7}),F2*-LOOKUP(D2,{1,2,3,4},{0.1,0.1,0.2,0.3})))[/SIZE]
 
Upvote 0
Still shorter formula, although perhaps not as maintainable:

Code:
=IF(F2<0,MID("4567",D2,1),-F2*MID("1123",D2,1))/10*NOT(OR(AND(D2>=3,F2=-1),AND(D2=4,F2=-2)))
 
Upvote 0
Thanks all for your excellent responses and apologies for the ambiguity on OP. The formula provided by @tyija1995 works perfectly. I now need to explore 'choose' and 'sign' as I've not seen these before!

Thanks

Glad it works! CHOOSE and SIGN are pretty cool, I managed to integrate the 2 together also as SIGN will return -1, 0 or 1, so augmenting by +2 results in 1,2,3 which is perfect for CHOOSE function, as the CHOOSE function will then return a result based on the index number! Seeing as your first column has values 1 through 4 (integers) this sets up CHOOSE to be ideal. All-in-all it worked out well using a combination of both.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top