|
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 |
Answers needed for challenging formula
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 "Sum Limit and marking" wrote in message ... 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 |
Answers needed for challenging formula
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 |
Answers needed for challenging formula
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),O FFSET(A1,1,MATCH(H1,$B$1:$F$1,1)+1))) -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=532916 |
Answers needed for challenging formula
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" a écrit dans le message de ... 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 |
Answers needed for challenging formula
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 |
Answers needed for challenging formula
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 |
Answers needed for challenging formula
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 |
Answers needed for challenging formula
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 |
Answers needed for challenging formula
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___10 0000 Lot Price:___0____269___308___421___503_____585__10000 0 Add'l qty:_0.14___0.14__0.14___0.09__0.07____0.055___0.0 55 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 |
Answers needed for challenging formula
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),100000000 0000))) |
Answers needed for challenging formula
On Fri, 14 Apr 2006 05:57:01 -0700, 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 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 |
Answers needed for challenging formula
Ron Rosenfeld wrote...
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. |
Answers needed for challenging formula
On 16 Apr 2006 18:51:10 -0700, "Harlan Grove" wrote:
Ron Rosenfeld wrote... 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(A 2,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(Q ty), 10^307,INDEX(LotPrice,1,MATCH(A2,Qty)+1))) --ron |
Answers needed for challenging formula
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) ? |
Answers needed for challenging formula
On 16 Apr 2006 22:29:08 -0700, "Harlan Grove" 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 |
| All times are GMT +1. The time now is 08:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com