Answers needed for challenging formula

  • Thread starter Sum Limit and marking
  • Start date
S

Sum Limit and marking

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
 
S

Sum Limit and marking

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

Ron Coderre

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
 
D

duane

similar to Ron's but I included result for input less than minimum in
table (ie <500). For what it's worth, I think the op's break points
were incorrect.

=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),OFFSET(A1,1,MATCH(H1,$B$1:$F$1,1)+1)))
 
S

Sum Limit and marking

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.
 
R

Ron Coderre

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
 
S

Sum Limit and marking

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.
 
D

duane

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
 
R

Ron Coderre

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
 
H

Harlan Grove

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? ....
Click to expand...
....

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

Ron Rosenfeld

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
Click to expand...

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
 
H

Harlan Grove

Ron Rosenfeld wrote...
....
Your table doesn't have a column for Qty < 500.
Click to expand...

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))
Click to expand...
....

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.
 
R

Ron Rosenfeld

Ron Rosenfeld wrote...

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.

...

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.
Click to expand...

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
 
H

Harlan Grove

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)))
Click to expand...

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

MATCH(A2,Qty)=COLUMNS(Qty)

with the simpler, shorter, faster

A2>=MAX(Qty)

?
 
R

Ron Rosenfeld

Ron Rosenfeld wrote...
...

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

MATCH(A2,Qty)=COLUMNS(Qty)

with the simpler, shorter, faster

A2>=MAX(Qty)

?
Click to expand...

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
 
Top