Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
IF statement using number ranges
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
IF statement using number ranges
=if(a2=10,1,if(a2=15,2,"error")) -- Don Guillett Microsoft MVP Excel SalesAid Software "Quimera" wrote in message news:yhlhm.41271$PH1.18314@edtnps82... 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
IF statement using number ranges
"Quimera" wrote:
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")) 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 ----- "Quimera" wrote in message news:yhlhm.41271$PH1.18314@edtnps82... 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
IF statement using number ranges
Without IF's
=IF(ISNA(LOOKUP(A2,{10,14.99,139.99},{1,2,3})),"er ror",LOOKUP(A2,{10,14.99,139.99},{1,2,3})) Add more ranges to suit. Gord Dibben MS Excel MVP On Fri, 14 Aug 2009 22:10:38 GMT, "Quimera" wrote: 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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
IF statement using number ranges
This works...
=IF(A25999,"4",IF(A21500,"3",IF(A2269,"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 "Quimera" wrote in message news:yhlhm.41271$PH1.18314@edtnps82... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple If Then Statement based on ranges | Excel Worksheet Functions | |||
Count the number between age ranges | Excel Discussion (Misc queries) | |||
Using named ranges in AdvacendFilter statement | Excel Discussion (Misc queries) | |||
COUNTIF Statement with Multiple Conditions in Different Ranges | Excel Worksheet Functions | |||
If Statement Between Ranges? | Excel Discussion (Misc queries) |