Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
return a value between set numbers
Hi I am trying to work out how to return a number of between 1 to 5 if a
single cell is between specific numbers. So i would like a A2 to equal 1 if A1 is between 0 and 48 or A2 to equal 2 if A1 is between 49 and 96 or A2 to equal 3 if A1 is between 97 and 144 or A2 to equal 4 if A1 is between 145 and 192 or A2 to equal 5 if A1 is between 193 and 240 any help would be appreciated. thank you in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
return a value between set numbers
Try this:
=IF(COUNT(A1),MATCH(A1,{0,49,97,145,193}),"") -- Biff Microsoft Excel MVP "Joe Black" wrote in message ... Hi I am trying to work out how to return a number of between 1 to 5 if a single cell is between specific numbers. So i would like a A2 to equal 1 if A1 is between 0 and 48 or A2 to equal 2 if A1 is between 49 and 96 or A2 to equal 3 if A1 is between 97 and 144 or A2 to equal 4 if A1 is between 145 and 192 or A2 to equal 5 if A1 is between 193 and 240 any help would be appreciated. thank you in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
return a value between set numbers
This formula will do it - I have included errors for negative
numbers / numbers over 240 =IF(A1=0,1,IF(A1<=240,IF(A1<0,"Error Number too low",ROUNDUP (A1/48,0)),"Error Number too high")) If you take the error checking out its a bit simpler: =IF(A1=0,1,ROUNDUP(A1/48,0)) On Dec 31, 12:13*pm, Joe Black wrote: Hi I am trying to work out how to return a *number of between 1 to 5 if a single cell is between specific numbers. So i would like a A2 to equal 1 if A1 is between 0 and 48 or A2 to equal 2 if A1 is between 49 and 96 or A2 to equal 3 if A1 is between 97 and 144 or A2 to equal 4 if A1 is between 145 and 192 or A2 to equal 5 if A1 is between 193 and 240 any help would be appreciated. thank you in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
return a value between set numbers
=LOOKUP(A2,{1,49,97,145,193;1,2,3,4,5})
"Joe Black" wrote: Hi I am trying to work out how to return a number of between 1 to 5 if a single cell is between specific numbers. So i would like a A2 to equal 1 if A1 is between 0 and 48 or A2 to equal 2 if A1 is between 49 and 96 or A2 to equal 3 if A1 is between 97 and 144 or A2 to equal 4 if A1 is between 145 and 192 or A2 to equal 5 if A1 is between 193 and 240 any help would be appreciated. thank you in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return the average of the LAST 3 numbers in a row | Excel Worksheet Functions | |||
how to return a set of numbers in column A | Excel Worksheet Functions | |||
how to return a set of numbers in column A | Excel Worksheet Functions | |||
how to return a set of numbers in column A | Excel Worksheet Functions | |||
Get different numbers to return text?? | Excel Worksheet Functions |