Graduated results

[Mobey]

I believe this formula is correct however it only displays results for <30 {0} and >30 {1000} what am I doing wrong?

My formula is:
=IF(B4>30%,1000,IF(B4>31%,2000,IF(B4>32%,3000,0)))

 

[Hui]

The issue is the order of the expressions in =IF(B4>30%,1000,IF(B4>31%,2000,IF(B4>32%,3000,0)))

If a value is 32% it is > 30% and so gets taken as True in the first If, it hence never gets to the second If check

Try:
=IF(B4>33%,4000,IF(B4>32%,3000,IF(B4>31%,2000,IF(B4>30%,1000,0))))

 

[Mobey]

Thanks Hui, so simple

 

[Khalid NGO]

Hi Mobey,

Few more:

=LOOKUP(B4,{0,0.31,0.32,0.33,0.34},{0,1,2,3,4})*1000

=CHOOSE(MATCH(B4,{0,0.31,0.32,0.33,0.34},1),0,1,2,3,4)*1000

Regards,

 

[bosco_yip]

Or,

=(MATCH(B4,{0,0.31,0.32,0.33,0.34})-1)*1000

=INT(MIN(MAX(B4-0.3,0)/1%,4))*1000

Regards
Bosco