Multiple nested IF help

[P_S]

Hi,

I am trying to use multiple nested if but I cannot do it right.

If(VDR1='X',
If([Cust Name]='A',
If(Sum([Net Revenue (Performance)]) >= 2000000, (Sum([Net Revenue (Performance)]))*0.5/100),
If(Sum([Net Revenue (Performance)]) >= 4000000, Sum([Net Revenue (Performance)])/100, 0)
)
IF([Cust Name]='B' OR 'C' OR 'D',
If(Sum([Net Revenue (Performance)]) >= 800000, (Sum([Net Revenue (Performance)]))*0.5/100),
If(Sum([Net Revenue (Performance)]) >= 2000000, Sum([Net Revenue (Performance)])/100, 0)
 )
If(VDR1='Y',
...
)

If I use only VDR1='X' and Cust Name = 'A' this will work fine.
But I want to do many VDR1 and in VDR1 I need to separate Cust Name into each Cust Name condition.

Any help would be much appreciated.

[JordyWegman]

Hi,

This is because you are closing the 3rd and 4th IFs with the ')'.

Try this:

If(VDR1='X',
    If([Cust Name]='A',
        If(Sum([Net Revenue (Performance)]) >= 2000000, (Sum([Net Revenue (Performance)])*0.5/100),
            If(Sum([Net Revenue (Performance)]) >= 4000000, Sum([Net Revenue (Performance)])/100, 0,

            )
        )
    )
)

 and continue where I stopped at the ,0,

Jordy

Climber

[P_S]

Hi Jordy,

Thank you. I appreciate your help. I don't know that I understand it correctly but this doesn't work for me.

If(VDR1='X',
If([Cust Name]='A',
If(Sum([Net Revenue (Performance)]) >= 2000000, (Sum([Net Revenue (Performance)]))*0.5/100),
If(Sum([Net Revenue (Performance)]) >= 4000000, Sum([Net Revenue (Performance)])/100, 0,

IF([Cust Name]='B' OR 'C' OR 'D',
If(Sum([Net Revenue (Performance)]) >= 800000, (Sum([Net Revenue (Performance)]))*0.5/100),
If(Sum([Net Revenue (Performance)]) >= 2000000, Sum([Net Revenue (Performance)])/100, 0)))))

Regards,

Panupong

[jonathandienst · Accepted solution]

A few observations:

• The syntax If([Cust Name]='B' OR 'C' OR 'D', is incorrect. Use If(Match([Cust Name], 'B', 'C', 'D'), ....
• I suspect your parentheses may be misplaced. I think you intend one rule for CustName = 'A' and another for B, C D. 
  That is not what your parentheses are doing.
• The order of these two comparisons are incorrect. If the result is more than 200k, then the first result will be true and the second will not be tested. If its less than 200k, then the  second test will always be false. Swap them round.

If(Sum([Net Revenue (Performance)]) >= 2000000, .....
  If(Sum([Net Revenue (Performance)]) >= 4000000, ....

I think this is what you need:

If(VDR1 = 'X',
	If([Cust Name] = 'A',	
		If(Sum([Net Revenue (Performance)]) >= 4000000, 
			Sum([Net Revenue (Performance)])/100,			
			If(Sum([Net Revenue (Performance)]) >= 2000000, 
				Sum([Net Revenue (Performance)])*0.5/100,
				0
			)
		),
				
		If(Match([Cust Name], 'B', 'C', 'D'),	
			If(Sum([Net Revenue (Performance)]) >= 2000000, 
				Sum([Net Revenue (Performance)])/100,
				If(Sum([Net Revenue (Performance)]) >= 800000, 
					Sum([Net Revenue (Performance)])*0.5/100,
					0
				)
			)
		)
	)	
)

[P_S]

Thank you for all guidance. You guys are the best!