On 16 Apr 2006 18:51:10 -0700, "Harlan Grove" <hrlngrv@aol.com> wrote:

>Ron Rosenfeld wrote...
>><SumLimitandmarking@discussions.microsoft.com> 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
>...
>>Your table doesn't have a column for Qty < 500.
>
>Which could mean that the minimum order quantity is 500 or the minimum
>order price is 269. At least that's what I assumed since 500 * 0.14 =
>70 << 269, so any rational buyer would buy in 400 unit lots if
>possible. The unit cost for orders of fewer than 500 would have to be
>at least .54 to make the 500 lot price lower for some order size < 500.
>That's a BIG jump from .14 per unit.
>
>>If you name your table PriceTbl, and the various rows as named in your table,
>>then, for values of 500 and greater, you could use the formula:
>>
>>=MIN(HLOOKUP(A2,PriceTbl,2)+HLOOKUP(A2,PriceTbl,3)*(A2-
>>HLOOKUP(A2,PriceTbl,1)),INDEX(LotPrice,1,MATCH(A2,Qty)+1))
>...
>
>This gives errors when A2 >= 5000, since the MATCH call would then
>return the last column index in Qty and LotPrice, so adding 1 to it
>would go outside range bounds.

Darn, thought I had checked that.

=MIN(HLOOKUP(A2,PriceTbl,2)+HLOOKUP(A2,PriceTbl,3)*(A2-
HLOOKUP(A2,PriceTbl,1)),IF(MATCH(A2,Qty)=COLUMNS(Qty),
10^307,INDEX(LotPrice,1,MATCH(A2,Qty)+1)))


--ron