If I understand you correctly, you want to sell your stock at a fixed price
for a Lot and an incremental price for partial lots...
BUT...if that price totals to more than the next size Lot price
THEN..charge the next size lot price instead.

If that's true, then here's one way:

With your table in A1:F3

For a quantitiy in H1

The price is
I1:
=MIN(HLOOKUP(H1,$B$1:$F$3,2,1)+(MAX(0,H1-HLOOKUP(H1,$B$1:$F$3,1,1))*HLOOKUP(H1,$B$1:$F$3,3,1)),INDEX($B$1:$F$3,2,MATCH(H1,$B$1:$F$1,1)+1))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Sum Limit and marking" wrote:

> I have a price chart with the following information:
>
> Qty: 500 1000 2500 3500 5000
> Lot Price: 269 308 421 503 585
> Add'l qty: .14 .14 .09 .07 .055
>
> I am looking for a formula that will give me the correct price depending on
> the quantity I type into the cell. However, my constraint is if a quantity
> is equal to 501, I need to multiply it by the Add'l qty cost of .14. At the
> same time if a qty goes above 786, using the Add'l qty cost will give me a
> price greater than the 1000 qty lot price. Instead I would like it to use the
> 1000 qty lot price since it is less. The same applies for when a qty is at
> 1801, 3414, and 4677.
>
> Does anyone have a suggestion that might solve my issue? Thanks