Nested IF Statements

Question

Hello,

I have a set of numbers in excel that range anywhere from 1 to 90 and I am trying to divide the numbers into buckets. I tried using the nested IF statement but I don't get the desired results instead I only get blank values, what could be causing this? Is there something wrong with my formula?

=IF(1<=O7<=10,"A",IF(10<O7<=30,"B",IF(30<O7<=50,"C",IF(50<O7<=70,"D",IF(70<O7<=90,"E"," ")))))

Answer 1

Hi,

In range A2:A6, type 1,10,30,50,70. In range B2:B6, type A,B,C,D,E. In cell P7, enter this formula

=vlookup(O7,$A$2:$B$6,2,1)

Hope this helps.

Answer 2

The function "Lookup" might be an option:

Answer 3

Here is the formula adjusted for your cell O7:

=IF(AND(O7>=1,O7<10),"A",IF(AND(O7>=10,O7<30),"B",IF(AND(O7>=30,O7<50),"C",IF(AND(O7>=50,O7<70),"D",IF(AND(O7>=70,O7<=90),"E"," ")))))

Rich~M

Answer 4

Hi BobRafat. I am an Excel user like you and will be glad to help.

There is a problem with the logical tests in your formula because they do not refer to a specific cell. You must also use an imbedded formula to evaluate 2 criteria as in more than and less than. The following formula will do what you are looking for.

=IF(AND(A2>=1,A2<10),"A",IF(AND(A2>=10,A2<30),"B",IF(AND(A2>=30,A2<50),"C",IF(AND(A2>=50,A2<70),"D",IF(AND(A2>=70,A2<=90),"E"," ")))))

Adjust it according to your spreadsheet for the correct column where your 1-90 numbers are located. I also set it to start the new categories at 1, 10, 30, 50, and 70 respectively. Adjust as necessary. I wasn't sure whether you wanted these numbers in the higher or lower category because your criteria above overlapped.

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

Answer 5

Hi BobRafat,

In Excel formula, 1<=O7<=10 is not a correct logic statement.

You should use AND function to combine these 2 logic.

So for instance:

=IF(AND(O7>=1,O7<=10),1,0)

Regards,

Alex Chen