IF statement using number ranges

  • Thread starter Thread starter Quimera
  • Start date Start date
Q

Quimera

I need a nested IF formula that can assign a code to a range of numbers.
There are 5 ranges.

For example...If (A2>=10 and <=14, "1", IF (A2>=15 and
<=140,"2"),IF(......."5"),"Error"))

I don't know how to define the range.

Thank you for your always helpful help.

Rose
 
Quimera said:
I need a nested IF formula that can assign a code
to a range of numbers.
[....]
For example...If (A2>=10 and <=14, "1", IF (A2>=15
and <=140,"2"),IF(......."5"),"Error"))
Click to expand...

It is unclear to me what the other ranges are. Since there are only 5, it
would have been helpful if you had simply fully specified them.

If the first two ranges top out at 14 and 140, should we assume that the
remaining 3 ranges top out at 1400, 14000 and 140000?

In any case, perhaps the following paradigm will be helpful.

=if(or(A2<10,140000<A2), "Error", lookup(A2, {10,15,141,1401,14001},
{"1","2","3","4","5"}))

However, do you really want __text_ results ("1", "2" etc)? Or do you want
numeric results (1, 2 etc)?

For numeric results, the paradigm above becomes:

=if(or(A2<10,140000<A2), "Error", lookup(A2, {10,15,141,1401,14001},
{1,2,3,4,5}))

And for the special case here where the result seems to be powers of 10
(plus 1) that 14 is multiplied by, there is a mathematical formula that
would avoid the lookup. I am relunctant to offer it because it is not as
general. It might just confuse things.


----- original message -----
 
Without IF's

=IF(ISNA(LOOKUP(A2,{10,14.99,139.99},{1,2,3})),"error",LOOKUP(A2,{10,14.99,139.99},{1,2,3}))

Add more ranges to suit.


Gord Dibben MS Excel MVP
 
This works...

=IF(A2>5999,"4",IF(A2>1500,"3",IF(A2>269,"3",IF(A2>10,"2","Error"))))

I had an Excel Function Reference book and the solution was included in
a sample of nested IF statements.

Thanks for all your help.

Rose
 
Back
Top