I am having trouble with a nested If Statement in Excel

Question

I am attempting to formulate a nested If Statement in Excel with three qualifications:

if sales are <100--> "Low"
if sales are between 100 and 1000-->"Mid"
if sales are greater than 1000-->"High"

Here's what I have so far: =IF(C3<100,"Low"), if(AND(C3>100,C3=1000)), "Mid"), if(C3>1000),"High"))

but the formula doesn't work. Can anyone help me out?

Best, Bryan McCormack

Answer 1

IF Trouble

Compare

• Here is your formula that doesn't work, and below it is the formula in your 'style' that would have worked, to see where you went wrong.

  =IF(C3<100,"Low"),IF(AND(C3>100,C3=1000)),"Mid"),IF(C3>1000),"High")) =IF(C3<100,"Low",IF(AND(C3>100,C3<=1000),"Mid",IF(C3>1000,"High","")))

Improve

• You can shorten the formula to the following:

  =IF(C3<100,"Low",IF(C3<=1000,"Mid","High"))

• You can add a check if there is a number in the cell:

  =IF(ISNUMBER(C3),IF(C3<100,"Low",IF(C3<=1000,"Mid","High")),"")

Issues (Whole Numbers Only)

Most of the following is incorrect for decimal numbers.

• What is this formula doing?
  =IF(C3<100,"Low",IF(C3<=1000,"Mid","High"))

  1st Case: Value is less than 100 i.e. ..., 97, 98, 99. .
  2nd Case: Value is between 99 and 1001 i.e. 100, 101, 102,..., 998, 999, 1000. .
  You could say Value is from 100 to 1000.
  3rd Case: Value is greater than 1000 i.e. 1001, 1002, 1003,... .

• Usually the same operators are used in such cases i.e. either:

  =IF(C3<=100,"Low",IF(C3<=1000,"Mid","High"))

  1st Case: Value is less than 101 i.e. ..., 98, 99, 100..
  2nd Case: Value is between 100 and 1001 i.e. 101, 102, 103,..., 998, 999, 1000. .
  You could say Value is from 101 to 1000.
  3rd Case: Value is greater than 1000 i.e. 1001, 1002, 1003,... .

• or:

  =IF(C3<100,"Low",IF(C3<1000,"Mid","High"))

  1st Case: Value is less than 100 i.e. ..., 97, 98, 99. .
  2nd Case: Value is between 99 and 1000 i.e. 100, 101, 102,..., 997, 998, 999. .
  You could say Value is from 100 to 999.
  3rd Case: Value is greater than 999 i.e. 1000, 1001, 1002,... .

Notice the small differences in the formulas.

Answer 2

Use this formula:

=if(C3<100, "Low", if(and(C3>=100,C3<=1000), "Mid","High"))

If it is not low and mid, then it's high

Answer 3

Assuming your values in Column C are only numbers, this will do

=IF(C3<100, "Low", IF(C3>1000, "High", "Mid"))