Urgent help on IF please!

[cimbom]

How can I create an IF formula that shows that if the number is
between 0 and 500 it is 0, if it is between 500 and 1000 it is 1, if
it is between 1000 and 1500 it is 2. For example: H5 calculates
number of gallons. And, I5 should calculate that if H5 is between 0
and 500 it is 0, if H5 is between 500 and 1000 it is 1, if it is
between 1000 and 1500 it is 2. I did this, but it does not work
=IF(H5>=500,1,IF(H5>=1000,2,IF(H5>=1500,3))) How can I do that? I
would appreciate your help. Thanks.

 

[Rick Rothstein \(MVP - VB\)]

How can I create an IF formula that shows that if the number is

between 0 and 500 it is 0, if it is between 500 and 1000 it is 1, if
it is between 1000 and 1500 it is 2. For example: H5 calculates
number of gallons. And, I5 should calculate that if H5 is between 0
and 500 it is 0, if H5 is between 500 and 1000 it is 1, if it is
between 1000 and 1500 it is 2. I did this, but it does not work
=IF(H5>=500,1,IF(H5>=1000,2,IF(H5>=1500,3))) How can I do that? I
would appreciate your help. Thanks.

=INT(H5/500)

Rick

 

[JE McGimpsey]

One way:

=MIN(INT(H5/500),3)

Note that in your formula, if H5 = 1000, then H5 is also >=500, so the
true branch is taken. You could reformat your IF() function:

=IF(H5<500,0,IF(H5<=100,1,IF(H5<=1500,2,3)))

 

[Guest]

=IF(H5>1500,3,IF(H5>1000,2,IF(H5>500,1,0)))

 

[JE McGimpsey]

How can I create an IF formula that shows that if the number is
between 0 and 500 it is 0, if it is between 500 and 1000 it is 1, if
it is between 1000 and 1500 it is 2. For example: H5 calculates
number of gallons. And, I5 should calculate that if H5 is between 0
and 500 it is 0, if H5 is between 500 and 1000 it is 1, if it is
between 1000 and 1500 it is 2. I did this, but it does not work
=IF(H5>=500,1,IF(H5>=1000,2,IF(H5>=1500,3))) How can I do that? I
would appreciate your help. Thanks.

=INT(H5/500)
[/QUOTE]

Note that this works fine as long as H5<2000, but if H5 is ever >=2000
will return a value > 3. Of course, that may be what you want, but it
isn't in your specification.

 

[Rick Rothstein \(MVP - VB\)]

How can I create an IF formula that shows that if the number is

Note that this works fine as long as H5<2000, but if H5 is ever >=2000
will return a value > 3. Of course, that may be what you want, but it
isn't in your specification.

I thought he simply got tired of type.<g>

Good point, though... I shouldn't have assumed that.

Rick

 

[Rick Rothstein \(MVP - VB\)]

=INT(H5/500)

I thought he simply got tired of type.<g>

And the reason I thought that was because the OP started by saying...

and then later on gave this example...

adding the H5>=1500 producing 3 condition.

Rick