If formula with many conditions

**gnoke** · 08-12-2010, 05:25 AM

Hi,

Original Post as I remember it:
I have a sheet with values in row 1.
The values are in the range of -100 to 100.
I am trying to create a IF formula to categorize the values in line A by ranges.
Eg. If value in cell B1 is equal to or greater than -100 and less than -80, the IF soultion is "-5"
If value in cell B1 is equal to or greater than -80 and less than -60, the IF soultion is "-4"
....
If value in cell B1 is equal to or greater than 40 and less than 60, the IF soultion is "3"
.....

Thanks to DonkeyOte

Gnoke

**DonkeyOte** · 08-12-2010, 05:52 AM

Not very clear I'm afraid - the bands seem a little inconsistent but perhaps:

=SIGN(A1)*CEILING(ABS(A1);20)/20

edit: always good to waste some time...

**gnoke** · 08-12-2010, 06:52 AM

Thanks DonkeyOte,
For me your help was not a waste of time, I needed a similiar formula in the sheet after all

**NBVC** · 08-12-2010, 08:00 AM

gnoke, please repost the original question.. as perhaps it could help someone else in the future with similar problem and they will have DonkeyOte's helpful solution.

thanks.