IF Formula to categorise amounts

[stevo365]

Hi All,

Im trying to categorise clients by the amount of revenue they generate. Ive created an IF formula to do this but it doesnt seem to be working. So far I have the IF formula below:

=IF(T4>20000,"Gold",IF(T4>15000,"Silver",IF(T4>10000,"Green",IF(T4>5000,"A",IF(T4>2500,"B",IF(T4>1500,"C","D"))))))

Which refers to Cell T4 where the amounts are, the amounts are worked out using the formula below which is in T4:

=IF(SUM(AS4:BD4)>0,SUM(AS4:BD4),"")

The problem is im getting a result of 'Gold' for the clients that have 0 revenue (which means the cell would be blank). How can this be as the IF formula should only return 'Gold' if the amount is over 20000..

Any ideas?

Thanks

[daddylonglegs]

Hello stevo,

When you use this formula

=IF(SUM(AS4:BD4)>0,SUM(AS4:BD4),"")

the "" (known as a "formula blank") is a text value. All text values are deemed to be "greater" than any number in excel so "">20000.

Perhaps just use

=SUM(AS4:BD4)

and format the cell to not show any zeroes or negatives, e.g. custom format as

general;;

make sure you include the two semi-colons......

...or add another condition to your IF formula, i.e

=IF(T4="","", original_formula)

[NBVC]

If you are dealing with whole numbers, then perhaps you can change your IF() formula to:

Please Login or Register  to view this content.

[stevo365]

thanks guys! Both worked great!