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
X5.00

There are therefore 18 categories.

I can do this using an IF formula:

=IF(B75.00,"HIGH",IF(B74.75,"5.00",IF(B74.50,"4 .75",IF(B74.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(B75,"High",IF(B7<0.75,"Error",CEILING(B7,0.25 )))

If you really wanted text, you could use:
=IF(B75,"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.)



Neil Pearce wrote:

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
X5.00

There are therefore 18 categories.

I can do this using an IF formula:

=IF(B75.00,"HIGH",IF(B74.75,"5.00",IF(B74.50,"4 .75",IF(B74.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

--

Dave Peterson