if statements, and statements
 

I have the following data:

QTY PRICE
<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?

if statements, and statements
 

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.

Pete

if statements, and statements
 

Suggestion
change your left hand column to show the bottom end of each range only,
staring with 0, like so, in columns A & B


Row 1: 0 100
Row 2: 100 95
Row 3: 501 90
Row 4: 1001 85

Then use the formula

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

BTW, 550 is priced at 90, not 95



"Sum Limit and marking" wrote:

I have the following data:

QTY PRICE
<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?

if statements, and statements
 

=SUMPRODUCT(--(--LEFT(A2:A5,FIND("-",A2:A5)-1)<=C1),--(--MID(A2:A5,FIND("-",
A2:A5)+1,255)=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)

"Sum Limit and marking" wrote
in message ...
I have the following data:

QTY PRICE
<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?