Failing with formula

  • Thread starter Thread starter Charlie
  • Start date Start date
C

Charlie

Hi,

I need to create a formula the will give the following results:
IF B8 is between 1 and 5000 the result is 1, if B8 is between 5001 and 10000
the result is 2 and so on.

Any help would be appreciated.

Many thanks

Charlie
 
Hey Charlie

You could do something like this:

=IF(AND(B8>1,B8<5001),1,IF(AND(B8>5000,B8<10001),2,"Outside Range"))

This has one IF statement nested within another to handle your two listed
requirements, and if over 10000 then will display text 'Outside Range'. Nest
further IF statements if you need to.

Regards,

Lee

You are limited to 7 nested IF statements, not sure if this is still the
case in the latest Excel version, so if you need to go beyond 7 levels of
checking then another approach is probably needed.
 
Note that Lee's formula will give "Outside Range" for a B8 value of 5000, or
of 5001, or of any value between 5000 and 5001. You may wish to think
carefully about how you define your limits.
Some of the alternative methods suggested may be better.
 
Hi David

I agree that the other suggestions are far better.

I can only get 'Outside Range' displayed for < 0 or > 10000, I cannot
replicate your results for the 5000 - 5001 values.

Don't want this to go off topic here as this is Charlies post, but, I did
get a problem with the value of 1 - ooops, so here's the updated:

=IF(AND(B8>0,B8<5001),1,IF(AND(B8>5000,B8<10001),2,"Outside Range"))

Regards,

Lee
 
Yes, you're right, Lee. I was misreading the formula, but in that case
5000.9 is giving 1, which may or may not be what the OP wanted, so again the
OP needs to be careful with defining the limits.
 
Back
Top