software

I have the following data:

QTY PRICE
lt;100 100
100-500 95
501-1000 90
1000-2500 85

A function that I am looking for is if I type in a quantity of 550 in a
cell, I want it to be able to retreive the correct pricing for whatever
quantity I request. In this case I want it to give me a price of 95. But I
also want to be able to use the same cell to request a quantity of 1000 and
to be able to give me back the correct price of 85.

Does anyone have a suggestion?

You can use VLOOKUP( ) for this. Type in your table somewhere, say L1
to M4, so it looks like this:

0 100
100 95
501 90
1000 85

If you use A1 to type in the quantity, then put this formula in B1:

=VLOOKUP(A1,L$1:M$4,2)

and this will give you the price from your table (Note your description
is not consistent - 550 should have a price of 90. Also, the quantity
1000 is ambiguous - I've assumed 1000 and upwards gives a price of 85).

Hope this helps.

PeteSuggestion
change your left hand column to show the bottom end of each range only,
staring with 0, like so, in columns A amp; BRow 1: 0100
Row 2: 10095
Row 3: 50190
Row 4: 100185

Then use the formula

=VLOOKUP(550,$a$1:$b$4,2)

BTW, 550 is priced at 90, not 95
quot;Sum Limit and markingquot; wrote:

gt; I have the following data:
gt;
gt; QTY PRICE
gt; lt;100 100
gt; 100-500 95
gt; 501-1000 90
gt; 1000-2500 85
gt;
gt; A function that I am looking for is if I type in a quantity of 550 in a
gt; cell, I want it to be able to retreive the correct pricing for whatever
gt; quantity I request. In this case I want it to give me a price of 95. But I
gt; also want to be able to use the same cell to request a quantity of 1000 and
gt; to be able to give me back the correct price of 85.
gt;
gt; Does anyone have a suggestion?

=SUMPRODUCT(--(--LEFT(A2:A5,FIND(quot;-quot;,A2:A5)-1)lt;=C1),--(--MID(A2:A5,FIND(quot;-quot;,
A2:A5) 1,255)gt;=C1),B2:B5)

you will need to change the first range to 0-100, and have unique ranges
that do not overlap.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Sum Limit and markingquot; gt; wrote
in message ...
gt; I have the following data:
gt;
gt; QTY PRICE
gt; lt;100 100
gt; 100-500 95
gt; 501-1000 90
gt; 1000-2500 85
gt;
gt; A function that I am looking for is if I type in a quantity of 550 in a
gt; cell, I want it to be able to retreive the correct pricing for whatever
gt; quantity I request. In this case I want it to give me a price of 95. But
I
gt; also want to be able to use the same cell to request a quantity of 1000
and
gt; to be able to give me back the correct price of 85.
gt;
gt; Does anyone have a suggestion?