need help with lookup formula

[Gil]

i am trying to enter a formula that will return a specific number for
a range of numbers.

Example:
i know that if i have between a range of items, that i need to return
a specific number for that formula. if the number is between 0 and 53
it needs to return a 4, if the number is between 54 and 65 it needs to
return a 5 and so on.

i tried using lookup and choose, but i get errors in the
formula....this is the formula i used.

=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,173},
{4,5,6,7,8,9,10,11})

E45 is the cell with the calculated value that now needs to be checked
to return the number i am looking for.

in this case, E45 has a 35 in it, so the formula should return a 4,
but instead i get a #N/A

 

[Teethless mama]

Try like this:

=LOOKUP(E45,{0,54.......},{4,5........}

 

[Ron Coderre]

Re:
=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,173},{4,5,6,7,8,9,10,11})
You'd need to have the same number of elements
in the two array sections of your formula.

Perhaps like this:
=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,173},{4,5,6,7,8,9,10,11,12,13,14,15})

But maybe this will work for you:
=MAX(FLOOR((E45-5)/12,1),4)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[carlo]

i am trying to enter a formula that will return a specific number for
a range of numbers.

Example:
i know that if i have between a range of items, that i need to return
a specific number for that formula.  if the number is between 0 and 53
it needs to return a 4, if the number is between 54 and 65 it needs to
return a 5 and so on.

i tried using lookup and choose, but i get errors in the
formula....this is the formula i used.

=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,173},
{4,5,6,7,8,9,10,11})

E45 is the cell with the calculated value that now needs to be checked
to return the number i am looking for.

in this case, E45 has a 35 in it, so the formula should return a 4,
but instead i get a #N/A

Don't know if your values are examples, but if not...this should work:
=MAX(4,(((E45-5)-MOD(E45-5,12))/12))

hth
Carlo

 

[Gil]

Re:
=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,173},{4,5,6,7,8,9,10,11})
You'd need to have the same number of elements
in the two array sections of your formula.

Perhaps like this:
=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,173},{4,5,6,7,8,9,10,11,12 ,13,14,15})

But maybe this will work for you:
=MAX(FLOOR((E45-5)/12,1),4)

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

thank you,

both of the previous posts seem to do the trick....i am still trying
to figure out this forum...i posted a reply to author, but don't see
it up on the forum.

the addition of the 0 in the lookup formula before the 53 did the
trick and so did the other formula of =MAX(4,(((E45-5)-MOD(E45-5,12))/
12).

i don't understand what the second formula does, but i hope the author
can explain it to me.

the formula you gave me kind of worked for 35....but it is returning a
value of one less then it should. when i enter other numbers...such
as 63 it returned a 4 instead of a 5,

 

[carlo]

Hi Gil

No problem with the direct mail
if you click "Reply" you post in the forum
"Reply to author" sends a mail to the Author, without posting it.

I tried the shorter version of Ron as well, but you need to change
that too:
=MAX(FLOOR((E45-6)/12+1,1),4)

do you understand this formula? otherwise just ask.

cheers Carlo