Answers needed for challenging formula

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

Have a look in help index for LOOKUP or HLOOKUP

--
Don Guillett
SalesAid Software
[email protected]
"Sum Limit and marking" <[email protected]> wrote
in message news:[email protected]...
>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

I've tried using lookup and hlookup, but when a qty reaches the breakeven
point where the lot price is less then the Add'l cost I want the price to use
the lesser cost (the lost price).

"Don Guillett" wrote:

> Have a look in help index for LOOKUP or HLOOKUP
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Sum Limit and marking" <[email protected]> wrote
> in message news:[email protected]...
> >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
>
>
>

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

Here is a solution with INDEX and EQUIV formulae.
There are many staging columns you can hide

http://cjoint.com/?eoq5jc7SVC

HTH
--
AP

"Sum Limit and marking" <[email protected]> a
écrit dans le message de
news:[email protected]...
> 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

Ron,

Thanks for the help the equation works. The only problem I am having is
when I put in a qty of 5,000 or greater it gives me a "#REF" instead of
giving me the lot price or the add'l covers price. Is there a statement that
I can add so it gives me me these values?

Thanks.

"Ron Coderre" wrote:

> 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

Try this:

Add an additional column to your price structure table for impossibly large
values:
Continuing the example:
G1: 100000
G2: (blank)
G3: (blank)

Change the formula in I1 to:
I1:
=MIN(HLOOKUP(H1,$B$1:$G$3,2,1)+(MAX(0,H1-HLOOKUP(H1,$B$1:$G$3,1,1))*HLOOKUP(H1,$B$1:$G$3,3,1)),INDEX($B$1:$G$3,2,MATCH(H1,$B$1:$G$1,1)+1))

Note: the same technique can be used for values below 500...
eg inserting a column in front of the price table with a zero quantity and a
price for 0-499 items.

Does that help?

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

XL2002, WinXP-Pro


"Sum Limit and marking" wrote:

> Ron,
>
> Thanks for the help the equation works. The only problem I am having is
> when I put in a qty of 5,000 or greater it gives me a "#REF" instead of
> giving me the lot price or the add'l covers price. Is there a statement that
> I can add so it gives me me these values?
>
> Thanks.
>
> "Ron Coderre" wrote:
>
> > 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

Is there another way to do this by using the current price sturcture and not
adding another column? Is there something in the current formula that I can
add so it can calculate the pricing at 5,000 and 5,500?

Thanks for your help.

"Ron Coderre" wrote:

> Try this:
>
> Add an additional column to your price structure table for impossibly large
> values:
> Continuing the example:
> G1: 100000
> G2: (blank)
> G3: (blank)
>
> Change the formula in I1 to:
> I1:
> =MIN(HLOOKUP(H1,$B$1:$G$3,2,1)+(MAX(0,H1-HLOOKUP(H1,$B$1:$G$3,1,1))*HLOOKUP(H1,$B$1:$G$3,3,1)),INDEX($B$1:$G$3,2,MATCH(H1,$B$1:$G$1,1)+1))
>
> Note: the same technique can be used for values below 500...
> eg inserting a column in front of the price table with a zero quantity and a
> price for 0-499 items.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Sum Limit and marking" wrote:
>
> > Ron,
> >
> > Thanks for the help the equation works. The only problem I am having is
> > when I put in a qty of 5,000 or greater it gives me a "#REF" instead of
> > giving me the lot price or the add'l covers price. Is there a statement that
> > I can add so it gives me me these values?
> >
> > Thanks.
> >
> > "Ron Coderre" wrote:
> >
> > > 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

with layout in Ron's answer

=IF(H1<MIN(B1:F1),B2,MIN(HLOOKUP(H1,$B$1:$F$3,2)+(
H1-HLOOKUP(H1,$B$1:$F$3,1))*HLOOKUP(H1,$B$1:$F$3,3),O
FFSET(A1,1,MATCH(H1,$B$1:$F$1,1)+1)))

handles <500 and >5000


"Sum Limit and marking" wrote:

> Is there another way to do this by using the current price sturcture and not
> adding another column? Is there something in the current formula that I can
> add so it can calculate the pricing at 5,000 and 5,500?
>
> Thanks for your help.
>
> "Ron Coderre" wrote:
>
> > Try this:
> >
> > Add an additional column to your price structure table for impossibly large
> > values:
> > Continuing the example:
> > G1: 100000
> > G2: (blank)
> > G3: (blank)
> >
> > Change the formula in I1 to:
> > I1:
> > =MIN(HLOOKUP(H1,$B$1:$G$3,2,1)+(MAX(0,H1-HLOOKUP(H1,$B$1:$G$3,1,1))*HLOOKUP(H1,$B$1:$G$3,3,1)),INDEX($B$1:$G$3,2,MATCH(H1,$B$1:$G$1,1)+1))
> >
> > Note: the same technique can be used for values below 500...
> > eg inserting a column in front of the price table with a zero quantity and a
> > price for 0-499 items.
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "Sum Limit and marking" wrote:
> >
> > > Ron,
> > >
> > > Thanks for the help the equation works. The only problem I am having is
> > > when I put in a qty of 5,000 or greater it gives me a "#REF" instead of
> > > giving me the lot price or the add'l covers price. Is there a statement that
> > > I can add so it gives me me these values?
> > >
> > > Thanks.
> > >
> > > "Ron Coderre" wrote:
> > >
> > > > 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

Instead of forcing a solution to fit the way you want the data to work, I
recommend that you do yourself a favor and use a proper lookup table and the
associated formulas.

Here's the lookup table (A1:H4)

Qty:________0___500__1000__2500__3500____5000___100000
Lot Price:___0____269___308___421___503_____585__100000
Add'l qty:_0.14___0.14__0.14___0.09__0.07____0.055___0.055
MaxPrice:_269____308__421____503___585__100000_____na

I1: (order qty)
J1:
=MIN(HLOOKUP(I1,$B$1:$H$4,2,1)+(MAX(0,I1-HLOOKUP(I1,$B$1:$H$4,1,1))*HLOOKUP(I1,$B$1:$H$4,3,1)),HLOOKUP(I1,$B$1:$H$4,4,1))

Is there a compelling reason to do otherwise?

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

XL2002, WinXP-Pro


"Sum Limit and marking" wrote:

> Is there another way to do this by using the current price sturcture and not
> adding another column? Is there something in the current formula that I can
> add so it can calculate the pricing at 5,000 and 5,500?
>
> Thanks for your help.
>
> "Ron Coderre" wrote:
>
> > Try this:
> >
> > Add an additional column to your price structure table for impossibly large
> > values:
> > Continuing the example:
> > G1: 100000
> > G2: (blank)
> > G3: (blank)
> >
> > Change the formula in I1 to:
> > I1:
> > =MIN(HLOOKUP(H1,$B$1:$G$3,2,1)+(MAX(0,H1-HLOOKUP(H1,$B$1:$G$3,1,1))*HLOOKUP(H1,$B$1:$G$3,3,1)),INDEX($B$1:$G$3,2,MATCH(H1,$B$1:$G$1,1)+1))
> >
> > Note: the same technique can be used for values below 500...
> > eg inserting a column in front of the price table with a zero quantity and a
> > price for 0-499 items.
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "Sum Limit and marking" wrote:
> >
> > > Ron,
> > >
> > > Thanks for the help the equation works. The only problem I am having is
> > > when I put in a qty of 5,000 or greater it gives me a "#REF" instead of
> > > giving me the lot price or the add'l covers price. Is there a statement that
> > > I can add so it gives me me these values?
> > >
> > > Thanks.
> > >
> > > "Ron Coderre" wrote:
> > >
> > > > 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

Sum Limit and marking wrote...
>Is there another way to do this by using the current price sturcture and not
>adding another column? Is there something in the current formula that I can
>add so it can calculate the pricing at 5,000 and 5,500?
....
>>>>"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
....

If the table above were named Tbl and the cell into which you enter the
quantity ordered were named Qty, then you could try

=IF(Qty<INDEX(Tbl,1,1),INDEX(Tbl,2,1),MIN(HLOOKUP(Qty,Tbl,2)
+(Qty-HLOOKUP(Qty,Tbl,1))*HLOOKUP(Qty,Tbl,3),IF(Qty<MAX(INDEX(Tbl,1,0)),
INDEX(Tbl,2,MATCH(Qty,INDEX(Tbl,1,0))+1),1000000000000)))

On Fri, 14 Apr 2006 05:57:01 -0700, Sum Limit and marking
<[email protected]> 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

Your table doesn't have a column for Qty < 500.

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))

If you want to include Qty amounts from 0-499, then you could use a formula
similar to:

=IF(A2<MIN(Qty),A2*MIN(AddLqty),MIN(HLOOKUP(A2,
PriceTbl,2)+HLOOKUP(A2,PriceTbl,3)*(A2-HLOOKUP(
A2,PriceTbl,1)),INDEX(LotPrice,1,MATCH(A2,Qty)+1)))

but the result of 499 being 27.45 and 500 being 269 doesn't really make sense.





--ron

Ron Rosenfeld wrote...
><[email protected]> 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.

On 16 Apr 2006 18:51:10 -0700, "Harlan Grove" <[email protected]> wrote:

>Ron Rosenfeld wrote...
>><[email protected]> 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" <[email protected]> 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