Complex IF formula

[fazthfc]

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,)))))))))

 

[jorismoerings]

Hi,

Check if this works for you:

Book1
	D	E	F	G
2	4		-2	0
Sheet1

Cell Formulas
Range	Formula
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)))

 

[salim hasan]

Try this Macro

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

 

[Special-K99]

@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

 

[tyija1995]

Hey, try this:

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!

 

[jorismoerings]

@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.

 

[fazthfc]

@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

 

[DanteAmor]

I show you another option:

[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]

 

[Eric W]

Still shorter formula, although perhaps not as maintainable:

=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)))

 

[tyija1995]

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.