IF Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Dear all,

I'm attempting to create a formula that categorises a number dependent on
its value:

<0.75
0.75<X<1
1<X<1.25...
....4.75<X<5.00
X>5.00

There are therefore 18 categories.

I can do this using an IF formula:

=IF(B7>5.00,"HIGH",IF(B7>4.75,"5.00",IF(B7>4.50,"4.75",IF(B7>4.25,"4.50",IF...
....(B7<0.75,"0.75","ERROR"))))))

However it is only possible to insert around 8 IF's for the FALSE return
before EXCEL returns an error with formula message.

Therefore how do I categorise dependent on 18 outcomes?


Kind regards,

Neil
 
Maybe you could use =ceiling() to do the real work:

=IF(B7>5,"High",IF(B7<0.75,"Error",CEILING(B7,0.25)))

If you really wanted text, you could use:
=IF(B7>5,"High",IF(B7<0.75,"Error",text(CEILING(B7,0.25),"0.00")))

(I'd use real numbers, but I'm not sure what you're doing next.)
 
Back
Top