IF B11 is less than 1000, enter 0 in D11, IF B11 is between...

[Guest]

Would you please help me create a formual that will return one of five values?

If B11 is less than 1000, enter 0 in D11
If B11 is more than 1000 and less than 2000, enter 1 in D11
If B11 is more than 2000 and less than 3000 enter 2 in D11
if B11 is more than 3000 and less than 4000 enter 3 in D11
if B11 is equal to or greater than 4000 enter 4 in D11

Thank you very very much!

 

[David Biddulph]

You haven't defined what you want if B11 is equal to 1000, or equal to 2000,
etc.
You could look in Excel help for the syntax of the IF function, and look at
the examples they give, which include examples of nested IF statements.
Another option is:
=MEDIAN(0,INT(B11/1000),4)

 

[Gord Dibben]

Try this in D11

=LOOKUP(B11,{0,1000,2000,3000,4000},{0,1,2,3,4})

You didn't say what to do with exactly 1000, 2000, 3000 or 4000

Are they considered as greater than?

The formula above treats as equal to or greater than.


Gord Dibben MS Excel MVP

 

[Guest]

Thank you for your help and thank you for pointing that out. You can't read
my mind! 1000 = 1, 2000 = 2, 3000=3, 4000=4.

 

[Guest]

Thank you too for pointing that out and for your help. When B11 = 1000, D11
=1, 2000=2, 3000=3 and 4000=4.