software

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

quot;Sum Limit and markingquot; gt; wrote
in message ...
gt;I have a price chart with the following information:
gt;
gt; Qty: 500 1000 2500 3500 5000
gt; Lot Price: 269 308 421 503 585
gt; Add'l qty: .14 .14 .09 .07 .055
gt;
gt; I am looking for a formula that will give me the correct price depending
gt; on
gt; the quantity I type into the cell. However, my constraint is if a
gt; quantity
gt; is equal to 501, I need to multiply it by the Add'l qty cost of .14. At
gt; the
gt; same time if a qty goes above 786, using the Add'l qty cost will give me a
gt; price greater than the 1000 qty lot price. Instead I would like it to use
gt; the
gt; 1000 qty lot price since it is less. The same applies for when a qty is
gt; at
gt; 1801, 3414, and 4677.
gt;
gt; 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).

quot;Don Guillettquot; wrote:

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

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-Proquot;Sum Limit and markingquot; wrote:

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


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

=IF(H1lt;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)))--
duane------------------------------------------------------------------------
duane's Profile: www.excelforum.com/member.php...oamp;userid=11624
View this thread: www.excelforum.com/showthread...hreadid=532916Here is a solution with INDEX and EQUIV formulae.
There are many staging columns you can hide

cjoint.com/?eoq5jc7SVC

HTH
--
AP

quot;Sum Limit and markingquot; gt; a
écrit dans le message de
...
gt; I have a price chart with the following information:
gt;
gt; Qty: 500 1000 2500 3500 5000
gt; Lot Price: 269 308 421 503 585
gt; Add'l qty: .14 .14 .09 .07 .055
gt;
gt; I am looking for a formula that will give me the correct price depending
on
gt; the quantity I type into the cell. However, my constraint is if a
quantity
gt; is equal to 501, I need to multiply it by the Add'l qty cost of .14. At
the
gt; same time if a qty goes above 786, using the Add'l qty cost will give me a
gt; price greater than the 1000 qty lot price. Instead I would like it to use
the
gt; 1000 qty lot price since it is less. The same applies for when a qty is
at
gt; 1801, 3414, and 4677.
gt;
gt; 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 quot;#REFquot; 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.

quot;Ron Coderrequot; wrote:

gt; If I understand you correctly, you want to sell your stock at a fixed price
gt; for a Lot and an incremental price for partial lots...
gt; BUT...if that price totals to more than the next size Lot price
gt; THEN..charge the next size lot price instead.
gt;
gt; If that's true, then here's one way:
gt;
gt; With your table in A1:F3
gt;
gt; For a quantitiy in H1
gt;
gt; The price is
gt; I1:
gt; =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))
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Sum Limit and markingquot; wrote:
gt;
gt; gt; I have a price chart with the following information:
gt; gt;
gt; gt; Qty: 500 1000 2500 3500 5000
gt; gt; Lot Price: 269 308 421 503 585
gt; gt; Add'l qty: .14 .14 .09 .07 .055
gt; gt;
gt; gt; I am looking for a formula that will give me the correct price depending on
gt; gt; the quantity I type into the cell. However, my constraint is if a quantity
gt; gt; is equal to 501, I need to multiply it by the Add'l qty cost of .14. At the
gt; gt; same time if a qty goes above 786, using the Add'l qty cost will give me a
gt; gt; price greater than the 1000 qty lot price. Instead I would like it to use the
gt; gt; 1000 qty lot price since it is less. The same applies for when a qty is at
gt; gt; 1801, 3414, and 4677.
gt; gt;
gt; gt; 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-Proquot;Sum Limit and markingquot; wrote:

gt; Ron,
gt;
gt; Thanks for the help the equation works. The only problem I am having is
gt; when I put in a qty of 5,000 or greater it gives me a quot;#REFquot; instead of
gt; giving me the lot price or the add'l covers price. Is there a statement that
gt; I can add so it gives me me these values?
gt;
gt; Thanks.
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; If I understand you correctly, you want to sell your stock at a fixed price
gt; gt; for a Lot and an incremental price for partial lots...
gt; gt; BUT...if that price totals to more than the next size Lot price
gt; gt; THEN..charge the next size lot price instead.
gt; gt;
gt; gt; If that's true, then here's one way:
gt; gt;
gt; gt; With your table in A1:F3
gt; gt;
gt; gt; For a quantitiy in H1
gt; gt;
gt; gt; The price is
gt; gt; I1:
gt; gt; =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))
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Sum Limit and markingquot; wrote:
gt; gt;
gt; gt; gt; I have a price chart with the following information:
gt; gt; gt;
gt; gt; gt; Qty: 500 1000 2500 3500 5000
gt; gt; gt; Lot Price: 269 308 421 503 585
gt; gt; gt; Add'l qty: .14 .14 .09 .07 .055
gt; gt; gt;
gt; gt; gt; I am looking for a formula that will give me the correct price depending on
gt; gt; gt; the quantity I type into the cell. However, my constraint is if a quantity
gt; gt; gt; is equal to 501, I need to multiply it by the Add'l qty cost of .14. At the
gt; gt; gt; same time if a qty goes above 786, using the Add'l qty cost will give me a
gt; gt; gt; price greater than the 1000 qty lot price. Instead I would like it to use the
gt; gt; gt; 1000 qty lot price since it is less. The same applies for when a qty is at
gt; gt; gt; 1801, 3414, and 4677.
gt; gt; gt;
gt; gt; gt; 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.

quot;Ron Coderrequot; wrote:

gt; Try this:
gt;
gt; Add an additional column to your price structure table for impossibly large
gt; values:
gt; Continuing the example:
gt; G1: 100000
gt; G2: (blank)
gt; G3: (blank)
gt;
gt; Change the formula in I1 to:
gt; I1:
gt; =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))
gt;
gt; Note: the same technique can be used for values below 500...
gt; eg inserting a column in front of the price table with a zero quantity and a
gt; price for 0-499 items.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Sum Limit and markingquot; wrote:
gt;
gt; gt; Ron,
gt; gt;
gt; gt; Thanks for the help the equation works. The only problem I am having is
gt; gt; when I put in a qty of 5,000 or greater it gives me a quot;#REFquot; instead of
gt; gt; giving me the lot price or the add'l covers price. Is there a statement that
gt; gt; I can add so it gives me me these values?
gt; gt;
gt; gt; Thanks.
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; If I understand you correctly, you want to sell your stock at a fixed price
gt; gt; gt; for a Lot and an incremental price for partial lots...
gt; gt; gt; BUT...if that price totals to more than the next size Lot price
gt; gt; gt; THEN..charge the next size lot price instead.
gt; gt; gt;
gt; gt; gt; If that's true, then here's one way:
gt; gt; gt;
gt; gt; gt; With your table in A1:F3
gt; gt; gt;
gt; gt; gt; For a quantitiy in H1
gt; gt; gt;
gt; gt; gt; The price is
gt; gt; gt; I1:
gt; gt; gt; =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))
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Sum Limit and markingquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I have a price chart with the following information:
gt; gt; gt; gt;
gt; gt; gt; gt; Qty: 500 1000 2500 3500 5000
gt; gt; gt; gt; Lot Price: 269 308 421 503 585
gt; gt; gt; gt; Add'l qty: .14 .14 .09 .07 .055
gt; gt; gt; gt;
gt; gt; gt; gt; I am looking for a formula that will give me the correct price depending on
gt; gt; gt; gt; the quantity I type into the cell. However, my constraint is if a quantity
gt; gt; gt; gt; is equal to 501, I need to multiply it by the Add'l qty cost of .14. At the
gt; gt; gt; gt; same time if a qty goes above 786, using the Add'l qty cost will give me a
gt; gt; gt; gt; price greater than the 1000 qty lot price. Instead I would like it to use the
gt; gt; gt; gt; 1000 qty lot price since it is less. The same applies for when a qty is at
gt; gt; gt; gt; 1801, 3414, and 4677.
gt; gt; gt; gt;
gt; gt; gt; gt; Does anyone have a suggestion that might solve my issue? Thanks

with layout in Ron's answer

=IF(H1lt;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 lt;500 and gt;5000quot;Sum Limit and markingquot; wrote:

gt; Is there another way to do this by using the current price sturcture and not
gt; adding another column? Is there something in the current formula that I can
gt; add so it can calculate the pricing at 5,000 and 5,500?
gt;
gt; Thanks for your help.
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try this:
gt; gt;
gt; gt; Add an additional column to your price structure table for impossibly large
gt; gt; values:
gt; gt; Continuing the example:
gt; gt; G1: 100000
gt; gt; G2: (blank)
gt; gt; G3: (blank)
gt; gt;
gt; gt; Change the formula in I1 to:
gt; gt; I1:
gt; gt; =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))
gt; gt;
gt; gt; Note: the same technique can be used for values below 500...
gt; gt; eg inserting a column in front of the price table with a zero quantity and a
gt; gt; price for 0-499 items.
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Sum Limit and markingquot; wrote:
gt; gt;
gt; gt; gt; Ron,
gt; gt; gt;
gt; gt; gt; Thanks for the help the equation works. The only problem I am having is
gt; gt; gt; when I put in a qty of 5,000 or greater it gives me a quot;#REFquot; instead of
gt; gt; gt; giving me the lot price or the add'l covers price. Is there a statement that
gt; gt; gt; I can add so it gives me me these values?
gt; gt; gt;
gt; gt; gt; Thanks.
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; If I understand you correctly, you want to sell your stock at a fixed price
gt; gt; gt; gt; for a Lot and an incremental price for partial lots...
gt; gt; gt; gt; BUT...if that price totals to more than the next size Lot price
gt; gt; gt; gt; THEN..charge the next size lot price instead.
gt; gt; gt; gt;
gt; gt; gt; gt; If that's true, then here's one way:
gt; gt; gt; gt;
gt; gt; gt; gt; With your table in A1:F3
gt; gt; gt; gt;
gt; gt; gt; gt; For a quantitiy in H1
gt; gt; gt; gt;
gt; gt; gt; gt; The price is
gt; gt; gt; gt; I1:
gt; gt; gt; gt; =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))
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Sum Limit and markingquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have a price chart with the following information:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Qty: 500 1000 2500 3500 5000
gt; gt; gt; gt; gt; Lot Price: 269 308 421 503 585
gt; gt; gt; gt; gt; Add'l qty: .14 .14 .09 .07 .055
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I am looking for a formula that will give me the correct price depending on
gt; gt; gt; gt; gt; the quantity I type into the cell. However, my constraint is if a quantity
gt; gt; gt; gt; gt; is equal to 501, I need to multiply it by the Add'l qty cost of .14. At the
gt; gt; gt; gt; gt; same time if a qty goes above 786, using the Add'l qty cost will give me a
gt; gt; gt; gt; gt; price greater than the 1000 qty lot price. Instead I would like it to use the
gt; gt; gt; gt; gt; 1000 qty lot price since it is less. The same applies for when a qty is at
gt; gt; gt; gt; gt; 1801, 3414, and 4677.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Does anyone have a suggestion that might solve my issue? Thanks