Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
A few observations:
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
)
)
)
)
)
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
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
A few observations:
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
)
)
)
)
)
Thank you for all guidance. You guys are the best!