Answers needed for challenging formula

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

Ron Rosenfeld wrote...
....
>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)))

Since you've named all the rows, why not replace

MATCH(A2,Qty)=COLUMNS(Qty)

with the simpler, shorter, faster

A2>=MAX(Qty)

?

On 16 Apr 2006 22:29:08 -0700, "Harlan Grove" <hrlngrv@aol.com> wrote:

>Ron Rosenfeld wrote...
>...
>>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)))
>
>Since you've named all the rows, why not replace
>
>MATCH(A2,Qty)=COLUMNS(Qty)
>
>with the simpler, shorter, faster
>
>A2>=MAX(Qty)
>
>?

Indeed. I was going to post that this morning. It was late last night when I
got your message.

Thanks.

Speaking of "faster", would something like 9e307 or 9.99e307 be faster than
10^307 ??


--ron