Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
I need a formula that can help me do the following:
I have a pricing grid in the following format, A B C D E 1 500 1000 2000 2500 2 64 $ 5 $ 6 $ 7 $ 8 3 80 $ 7 $ 8 $ 9 $10 4 96 $............................... 5 112 $............................... The numbers in column A are the number pages The numbers in row 1 are quantities of books Is there a formula that would allow me to type in the number of pages, and quantity, and provide me with the correct pricing? For example, a 64 page book at a quantity of 500 equals $5. Also if a book contains 80 pages with a quantity of 750, I need it to give me the next highest cost, which would be $8. Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
Try this:
A10 = lookup value pages B10 = lookup value quantity This will work as long as the quantity in B10 does not excede the max quantity listed in the table. For example, the max qty in the table is 2500. As long as the lookup value qty is not greater than 2500 this will work: =VLOOKUP(A10,A1:E5,CEILING(B10/500,1)+1,0) Biff "Sum Limit and marking" wrote in message ... I need a formula that can help me do the following: I have a pricing grid in the following format, A B C D E 1 500 1000 2000 2500 2 64 $ 5 $ 6 $ 7 $ 8 3 80 $ 7 $ 8 $ 9 $10 4 96 $............................... 5 112 $............................... The numbers in column A are the number pages The numbers in row 1 are quantities of books Is there a formula that would allow me to type in the number of pages, and quantity, and provide me with the correct pricing? For example, a 64 page book at a quantity of 500 equals $5. Also if a book contains 80 pages with a quantity of 750, I need it to give me the next highest cost, which would be $8. Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
The only problem with this formula is that the qty that it goes up by jumps
from 5,000 to 7,500. So this formula would not work because qty will not always increase in increments of 500. Is there another formula? Thanks. "Biff" wrote: Try this: A10 = lookup value pages B10 = lookup value quantity This will work as long as the quantity in B10 does not excede the max quantity listed in the table. For example, the max qty in the table is 2500. As long as the lookup value qty is not greater than 2500 this will work: =VLOOKUP(A10,A1:E5,CEILING(B10/500,1)+1,0) Biff "Sum Limit and marking" wrote in message ... I need a formula that can help me do the following: I have a pricing grid in the following format, A B C D E 1 500 1000 2000 2500 2 64 $ 5 $ 6 $ 7 $ 8 3 80 $ 7 $ 8 $ 9 $10 4 96 $............................... 5 112 $............................... The numbers in column A are the number pages The numbers in row 1 are quantities of books Is there a formula that would allow me to type in the number of pages, and quantity, and provide me with the correct pricing? For example, a 64 page book at a quantity of 500 equals $5. Also if a book contains 80 pages with a quantity of 750, I need it to give me the next highest cost, which would be $8. Any suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
Try this:
Let's say your citeria in G1 and H1 G1 =80 H1 =750 Formuala in I1 =IF(ISNA(MATCH(H1,A1:E1,0)),INDEX(A1:E5,MATCH(G1,A 1:A5,0),MATCH(H1,A1:E1,1)+1),INDEX(A1:E5,MATCH(G1, A1:A5,0),MATCH(H1,A1:E1,0))) "Sum Limit and marking" wrote: I need a formula that can help me do the following: I have a pricing grid in the following format, A B C D E 1 500 1000 2000 2500 2 64 $ 5 $ 6 $ 7 $ 8 3 80 $ 7 $ 8 $ 9 $10 4 96 $............................... 5 112 $............................... The numbers in column A are the number pages The numbers in row 1 are quantities of books Is there a formula that would allow me to type in the number of pages, and quantity, and provide me with the correct pricing? For example, a 64 page book at a quantity of 500 equals $5. Also if a book contains 80 pages with a quantity of 750, I need it to give me the next highest cost, which would be $8. Any suggestions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
Teethless mama,
I put your formula through, however, it gives me a NA. Any other suggestions that may work. Thanks. "Teethless mama" wrote: Try this: Let's say your citeria in G1 and H1 G1 =80 H1 =750 Formuala in I1 =IF(ISNA(MATCH(H1,A1:E1,0)),INDEX(A1:E5,MATCH(G1,A 1:A5,0),MATCH(H1,A1:E1,1)+1),INDEX(A1:E5,MATCH(G1, A1:A5,0),MATCH(H1,A1:E1,0))) "Sum Limit and marking" wrote: I need a formula that can help me do the following: I have a pricing grid in the following format, A B C D E 1 500 1000 2000 2500 2 64 $ 5 $ 6 $ 7 $ 8 3 80 $ 7 $ 8 $ 9 $10 4 96 $............................... 5 112 $............................... The numbers in column A are the number pages The numbers in row 1 are quantities of books Is there a formula that would allow me to type in the number of pages, and quantity, and provide me with the correct pricing? For example, a 64 page book at a quantity of 500 equals $5. Also if a book contains 80 pages with a quantity of 750, I need it to give me the next highest cost, which would be $8. Any suggestions? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
Tested and It works on my work sheet.
"Sum Limit and marking" wrote: I need a formula that can help me do the following: I have a pricing grid in the following format, A B C D E 1 500 1000 2000 2500 2 64 $ 5 $ 6 $ 7 $ 8 3 80 $ 7 $ 8 $ 9 $10 4 96 $............................... 5 112 $............................... The numbers in column A are the number pages The numbers in row 1 are quantities of books Is there a formula that would allow me to type in the number of pages, and quantity, and provide me with the correct pricing? For example, a 64 page book at a quantity of 500 equals $5. Also if a book contains 80 pages with a quantity of 750, I need it to give me the next highest cost, which would be $8. Any suggestions? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
Well, if you don't tell us ALL the details all we can do is go by what you
have posted. Tell us what *ALL* the Qty values are. Biff "Sum Limit and marking" wrote in message ... The only problem with this formula is that the qty that it goes up by jumps from 5,000 to 7,500. So this formula would not work because qty will not always increase in increments of 500. Is there another formula? Thanks. "Biff" wrote: Try this: A10 = lookup value pages B10 = lookup value quantity This will work as long as the quantity in B10 does not excede the max quantity listed in the table. For example, the max qty in the table is 2500. As long as the lookup value qty is not greater than 2500 this will work: =VLOOKUP(A10,A1:E5,CEILING(B10/500,1)+1,0) Biff "Sum Limit and marking" wrote in message ... I need a formula that can help me do the following: I have a pricing grid in the following format, A B C D E 1 500 1000 2000 2500 2 64 $ 5 $ 6 $ 7 $ 8 3 80 $ 7 $ 8 $ 9 $10 4 96 $............................... 5 112 $............................... The numbers in column A are the number pages The numbers in row 1 are quantities of books Is there a formula that would allow me to type in the number of pages, and quantity, and provide me with the correct pricing? For example, a 64 page book at a quantity of 500 equals $5. Also if a book contains 80 pages with a quantity of 750, I need it to give me the next highest cost, which would be $8. Any suggestions? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
Thanks for the formula it does work for certain quantities. When I put in a
quantity that matches to the quantity where the break even points are it gives me an NA. For example, when I put in a quantity of 750 for an 80 page book it gives me $8, but if I put in a quantity of 1,000 it gives me NA. Any reasone why? Thanks for you help. "Teethless mama" wrote: Tested and It works on my work sheet. "Sum Limit and marking" wrote: I need a formula that can help me do the following: I have a pricing grid in the following format, A B C D E 1 500 1000 2000 2500 2 64 $ 5 $ 6 $ 7 $ 8 3 80 $ 7 $ 8 $ 9 $10 4 96 $............................... 5 112 $............................... The numbers in column A are the number pages The numbers in row 1 are quantities of books Is there a formula that would allow me to type in the number of pages, and quantity, and provide me with the correct pricing? For example, a 64 page book at a quantity of 500 equals $5. Also if a book contains 80 pages with a quantity of 750, I need it to give me the next highest cost, which would be $8. Any suggestions? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup
nevermind. Looks like I accidentally use the incorrect cell. It works
great! Thanks for your help. "Teethless mama" wrote: Tested and It works on my work sheet. "Sum Limit and marking" wrote: I need a formula that can help me do the following: I have a pricing grid in the following format, A B C D E 1 500 1000 2000 2500 2 64 $ 5 $ 6 $ 7 $ 8 3 80 $ 7 $ 8 $ 9 $10 4 96 $............................... 5 112 $............................... The numbers in column A are the number pages The numbers in row 1 are quantities of books Is there a formula that would allow me to type in the number of pages, and quantity, and provide me with the correct pricing? For example, a 64 page book at a quantity of 500 equals $5. Also if a book contains 80 pages with a quantity of 750, I need it to give me the next highest cost, which would be $8. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |