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

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

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

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