Expert: Bob Umlas - 6/7/2004

Question
Hello,

Thanks doe the reply.  I have developed the formula a little further, however some of it works and some of it does not.

Works:  If value above 30, and if value between 20 and 29.  
Does not work if value between 10 and 19 and 0 and 9.

IF(D11>=30,4,IF(OR(D11>=20,D11<=29),"3",IF(OR(D11>=10,D11<=19),"2",IF(OR(D11>=0,D11<=9),1))))

Can you shed any light,

regards,

Imtiaz

-------------------------
Followup To
Question -
Hello,

Hope you can help,

I have this formula sitting in L10.

A10 - Refers to a cell where I would enter a colleague name.

IF(A10="","",)-  If the cell has a null value, return no value.


I now need to calcualte a target figure, based on a number in D10.

The following rules need to apply.

If in D10 the number is between 0 and 9, return a value of 1.

If in D10 the number is between 10 and 20, return a value of 2.

If in D10 the number is between 21 and 30, return a value of 3.

If in D10 the number is greater than 30, return a value of 4.

I have having trouble getting the multiple if statement to work.

Can you help.

Regards,

Imtiaz
Answer -
=IF(A10="","",IF(D10>30,4,IF(D10>20,3,IF(F10>10,2,IF(D10>=0,1)))))

Answer
Yes -- look again at the formula.
IF(D11>=30,4,IF(OR(D11>=20,D11<=29),"3",IF(OR(D11>=10,D11<=19),"2",IF(OR(D11>=0,D11<=9),1))))

Suppose D11 has 15 in it. Well, 15 IS <=29, so it satisfies the condition IF(OR(D11>=20,D11<=29), so it never goes any farther. ANY number will satisfy the condition OR(D11>=20,D11<=29). Yes, ANY number. -43, 819283, any.

You need AND, not OR:
IF(D11>=30,4,IF(AND(D11>=20,D11<=29),"3",IF(AND(D11>=10,D11<=19),"2",IF(AND(D11>=0,D11<=9),1))))

But I think my solution is easier, shorter, and works!