# Thread: Complex IF formula Thanks: 0 Likes:  2 Post #5329478 (1)Post #5329586 (1)

1. ## Complex IF formula

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,)))))))))  Reply With Quote

2. ## Re: Complex IF formula

Hi,

Check if this works for you:
DEFG
24-20

Sheet1

Worksheet Formulas
CellFormula
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)))  Reply With Quote

3. ## Re: Complex IF formula

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........
' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Select
[G2] = my_val
End Sub```  Reply With Quote

4. ## Re: Complex IF formula

@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  Reply With Quote

5. ## Re: Complex IF formula

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!  Reply With Quote

6. ## Re: Complex IF formula

@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.  Reply With Quote

7. ## Re: Complex IF formula Originally Posted by jorismoerings @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  Reply With Quote

8. ## Re: Complex IF formula

I show you another option:

Code:
`=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})))`  Reply With Quote

9. ## Re: Complex IF formula

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)))`  Reply With Quote

10. ## Re: Complex IF formula Originally Posted by fazthfc 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.  Reply With Quote

## User Tag List

complex, formula, greater, multiply, number 