Nested If statement when banding in group

[melvin]

team,

My formula works perfectly if I have some amount in the budgeted sale. As Soon as I put Zero in Sales the output is not what i expect it comes as 80+. i expect it to be Zero.

IF(AND(E5>-1,E5<0),"$0",IF(AND(E5>0,E5<18000),"$0 - $18k",IF(AND(E5>18000,E5<30000),"$18 - $30k",IF(AND(E5>30000,E5<40000),"$30 - $40k",IF(AND(E5>40000,E5<50000),"$40 - $50k",IF(AND(E5>50000,E5<60000),"$50 - $60k",IF(AND(E5>60000,E5<70000),"$60 - $70k","$80k +")))))))


Thanks team.

Melv

 

[Deepak]

Just change this.

AND(E5>-1,E5=0)

 

[Nunes]

Hi melvin!

You're working exclusively with "less than" and "greater than" clauses... for each interval, you should have at one of the ends a "or equal" clause.
What I mean is that your problem of wrong return when sales are zero will ocurr again, for example, if sales are 18000, or 30000, or 40000, .... or any of the values in the boundaries of your intervals.
This happens because you're testing Sales to be > than a value or < than a value.

You'll solve your "zero" problem by replacing "AND(E5>-1,E5<0)" by "AND(E5>-1,E5<=0)" in the begining of your problem.
But you should replace all "<" by "<="s, in my view...

Best
Nuno

 

[Deepak]

or this..


=IF(E5<=0,"$0",IF(E5<18000,"$0 - $18k",IF(E5<30000,"$18 - $30k",IF(E5<40000,"$30 - $40k",IF(E5<50000,"$40 - $50k",IF(E5<60000,"$50 - $60k",IF(E5<70000,"$60 - $70k","$80k +")))))))

 

[melvin]

Ah ok...Thanks Nuno and Deepak. deepak thanks for taking time to explain.

Regards,

melv.

 

[Deepak]

Ah ok...Thanks Nuno and Deepak. deepak thanks for taking time to explain.

Regards,

melv.

Nunes has explained....so say thanks to him/her for the same.

 

[Khalid NGO]

Hi Melvin,
It will ease you if you setup a lookup table for your group.

for example:
enter this in column K:
1
18000
30000
40000
50000
60000
70000

and this in column L:
$0 - $18k
"$18 - $30k"
"$30 - $40k"
"$40 - $50k"
"$50 - $60k"
"$60 - $70k"
"$80k +"

now use this simple lookup formula:
=IF(E5=0,"$0",LOOKUP(E5,K2:K8,L2:L8))

and in case if you dont want to use helper columns (not recommended) then use this hard coded:
=IF(E5=0,"$0",LOOKUP(E5,{1;18000;30000;40000;50000;60000;70000},{"$0 - $18k";"""$18 - $30k""";"""$30 - $40k""";"""$40 - $50k""";"""$50 - $60k""";"""$60 - $70k""";"""$80k +"""}))

Regards,