#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Another way to lookup data David Vollmer Excel Worksheet Functions 1 September 23rd 05 05:16 AM
Lookup Vector > Lookup Value Alec Kolundzic Excel Worksheet Functions 6 June 10th 05 02:14 PM
Lookup Function Problem seve Excel Discussion (Misc queries) 9 February 28th 05 03:46 AM
Lookup function w/Text and Year Josh O. Excel Worksheet Functions 1 February 13th 05 12:27 AM
double lookup, nest, or macro? Josef.angel Excel Worksheet Functions 1 October 29th 04 09:50 AM


All times are GMT +1. The time now is 09:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"