Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with lookup formula
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,1 73}, {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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with lookup formula
Try like this:
=LOOKUP(E45,{0,54.......},{4,5........} "Gil" wrote: 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,1 73}, {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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with lookup formula
=LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},{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,1 73},{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) "Gil" wrote in message ... 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,1 73}, {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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with lookup formula
On Jan 31, 10:50*am, Gil wrote:
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,1 73}, {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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with lookup formula
On Jan 30, 9:15*pm, "Ron Coderre"
wrote: =LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},{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,1 73},{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) "Gil" wrote in message ... 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,1 73}, {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 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, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need help with lookup formula
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 On Jan 31, 11:55*am, Gil wrote: On Jan 30, 9:15*pm, "Ron Coderre" wrote: =LOOKUP(E45,{53,65,77,89,101,113,125,137,149,161,1 73},{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,1 73},{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) "Gil" wrote in message ... 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,1 73}, {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 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,- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup formula | Excel Discussion (Misc queries) | |||
row lookup formula | Excel Worksheet Functions | |||
lookup formula? | Excel Worksheet Functions | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions | |||
If the lookup-value is a formula | Excel Worksheet Functions |