Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing - Sliding Scale Price Markup
In Excel, I am looking for a formula to apply a sliding scale price markup
using interpolation. The size of the percentage markup would decrease as cost increases and would increase as cost decreases. I have 200 products and the cost ranges from $1.50 (approx. 60% markup) to $35.00 (approx. 15% markup) Avg. cost is $6.00 (50% markup) Thank you. Ed. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing - Sliding Scale Price Markup
On Aug 27, 2:18 pm, "Ed" wrote:
In Excel, I am looking for a formula to apply a sliding scale price markup using interpolation. The size of the percentage markup would decrease as cost increases and would increase as cost decreases. I have 200 products and the cost ranges from $1.50 (approx. 60% markup) to $35.00 (approx. 15% markup) Avg. cost is $6.00 (50% markup) Thank you. Ed. Guess it depends how you want to do it. You need to select an amount where there is a change in the markup %. It may be every $1, or a true sliding scale would use your smallest possible dollar increment on your cost list. So maybe $.01 or $.001. And, I'd probably find the best would be to make a simple table, although you could cram all this into a formula. Personally, I'd want the table to have for printing or visually checking a markup on occasion, anyway. List all your prices and the markup in 2 columns. Then use INDEX/MATCH in your invoice worksheet to get the proper markup. If you use more of a jump than your smallest increment, make sure to put $35 at the top and $1.50 at the bottom to allow the formula to work best. By $.25 increments, and rounded for sanity: ...A...........B 35.00....15.00% 34.75....15.34% 34.50....15.67% 34.25....16.01% Lookup function to pull the markup, assuming E2 is your price: =INDEX($A$1:$B$135,MATCH(E2,$A$1:$A$135,-1),2) And the formula to figure out the difference is just simple division. Well, 2 of them. Using my $.25 increment: (60%-15%)/((35-1.50)/.25)=.003358209 Add that to each level of markup. So the first dollar level has 15% markup, the 2nd is 15.34%, 15.67%, etc. (rounded, of course) Or you could go truly nuts and base it on percentage of products above and below your average cost of $6. Have it be a weighted sliding scale. That would be more complex and you didn't give enough data to play with that. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing - Sliding Scale Price Markup
John McGimpsey show how to calculate a variable rate.
May be something you could use. http://www.mcgimpsey.com/excel/variablerate.html Gord Dibben MS Excel MVP On Wed, 27 Aug 2008 12:18:55 -0700, "Ed" wrote: In Excel, I am looking for a formula to apply a sliding scale price markup using interpolation. The size of the percentage markup would decrease as cost increases and would increase as cost decreases. I have 200 products and the cost ranges from $1.50 (approx. 60% markup) to $35.00 (approx. 15% markup) Avg. cost is $6.00 (50% markup) Thank you. Ed. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing - Sliding Scale Price Markup
Thank you so much for your help.
Ed. "Spiky" wrote in message ... On Aug 27, 2:18 pm, "Ed" wrote: In Excel, I am looking for a formula to apply a sliding scale price markup using interpolation. The size of the percentage markup would decrease as cost increases and would increase as cost decreases. I have 200 products and the cost ranges from $1.50 (approx. 60% markup) to $35.00 (approx. 15% markup) Avg. cost is $6.00 (50% markup) Thank you. Ed. Guess it depends how you want to do it. You need to select an amount where there is a change in the markup %. It may be every $1, or a true sliding scale would use your smallest possible dollar increment on your cost list. So maybe $.01 or $.001. And, I'd probably find the best would be to make a simple table, although you could cram all this into a formula. Personally, I'd want the table to have for printing or visually checking a markup on occasion, anyway. List all your prices and the markup in 2 columns. Then use INDEX/MATCH in your invoice worksheet to get the proper markup. If you use more of a jump than your smallest increment, make sure to put $35 at the top and $1.50 at the bottom to allow the formula to work best. By $.25 increments, and rounded for sanity: ..A...........B 35.00....15.00% 34.75....15.34% 34.50....15.67% 34.25....16.01% Lookup function to pull the markup, assuming E2 is your price: =INDEX($A$1:$B$135,MATCH(E2,$A$1:$A$135,-1),2) And the formula to figure out the difference is just simple division. Well, 2 of them. Using my $.25 increment: (60%-15%)/((35-1.50)/.25)=.003358209 Add that to each level of markup. So the first dollar level has 15% markup, the 2nd is 15.34%, 15.67%, etc. (rounded, of course) Or you could go truly nuts and base it on percentage of products above and below your average cost of $6. Have it be a weighted sliding scale. That would be more complex and you didn't give enough data to play with that. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing - Sliding Scale Price Markup
Thank you so much for your help.
Ed. "Gord Dibben" <gorddibbATshawDOTca wrote in message ... John McGimpsey show how to calculate a variable rate. May be something you could use. http://www.mcgimpsey.com/excel/variablerate.html Gord Dibben MS Excel MVP On Wed, 27 Aug 2008 12:18:55 -0700, "Ed" wrote: In Excel, I am looking for a formula to apply a sliding scale price markup using interpolation. The size of the percentage markup would decrease as cost increases and would increase as cost decreases. I have 200 products and the cost ranges from $1.50 (approx. 60% markup) to $35.00 (approx. 15% markup) Avg. cost is $6.00 (50% markup) Thank you. Ed. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing - Sliding Scale Price Markup
I found the following formula, using interpolation. It will start by
assigning a 50% markup to $1.00 and the percentage will go down from there until it reaches $39.99; and will start at 12% for anything above $40.00. =IF(A1<=39.99,(-A1/100+0.50+1/100),0.12) I would like to know how I can add to the formula so that it starts and ends at 14 different intervals: $1.00-$1.99 assign 50% $2.00-$2.99 assign 49% $3.00-$3.99 assign 48% $4.00-$4.99 assign 47% $5.00-$5.99 assign 46% $6.00-$6.99 assign 45% $7.00-$7.99 assign 44% $8.00-$8.99 assign 43% $9.00-$9.99 assign 42% $10.00-$10.99 assign 41% $11.00-$11.99 assign 40% $12.00-$12.99 assign 39% $13.00-$13.99 assign 38% $14.00-$14.99 assign 37% $15.00-$15.99 assign 36% $16.00-$16.99 assign 34% $17.00-$17.99 assign 32% $18.00-$18.99 assign 30% $19.00-$19.99 assign 25% $20.00-$24.99 assign 20% $25.00-$29.99 assign 18% $30.00-$34.99 assign 16% $35.00-$39.99 assign 14% $40.00+ assign 12% Thank you. Ed. "Spiky" wrote in message ... On Aug 27, 2:18 pm, "Ed" wrote: In Excel, I am looking for a formula to apply a sliding scale price markup using interpolation. The size of the percentage markup would decrease as cost increases and would increase as cost decreases. I have 200 products and the cost ranges from $1.50 (approx. 60% markup) to $35.00 (approx. 15% markup) Avg. cost is $6.00 (50% markup) Thank you. Ed. Guess it depends how you want to do it. You need to select an amount where there is a change in the markup %. It may be every $1, or a true sliding scale would use your smallest possible dollar increment on your cost list. So maybe $.01 or $.001. And, I'd probably find the best would be to make a simple table, although you could cram all this into a formula. Personally, I'd want the table to have for printing or visually checking a markup on occasion, anyway. List all your prices and the markup in 2 columns. Then use INDEX/MATCH in your invoice worksheet to get the proper markup. If you use more of a jump than your smallest increment, make sure to put $35 at the top and $1.50 at the bottom to allow the formula to work best. By $.25 increments, and rounded for sanity: ..A...........B 35.00....15.00% 34.75....15.34% 34.50....15.67% 34.25....16.01% Lookup function to pull the markup, assuming E2 is your price: =INDEX($A$1:$B$135,MATCH(E2,$A$1:$A$135,-1),2) And the formula to figure out the difference is just simple division. Well, 2 of them. Using my $.25 increment: (60%-15%)/((35-1.50)/.25)=.003358209 Add that to each level of markup. So the first dollar level has 15% markup, the 2nd is 15.34%, 15.67%, etc. (rounded, of course) Or you could go truly nuts and base it on percentage of products above and below your average cost of $6. Have it be a weighted sliding scale. That would be more complex and you didn't give enough data to play with that. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing - Sliding Scale Price Markup
On Sep 1, 2:42 pm, "Ed" wrote:
I found the following formula, using interpolation. It will start by assigning a 50% markup to $1.00 and the percentage will go down from there until it reaches $39.99; and will start at 12% for anything above $40.00. =IF(A1<=39.99,(-A1/100+0.50+1/100),0.12) I would like to know how I can add to the formula so that it starts and ends at 14 different intervals: Did you mean 24 percent groups since you listed 24? The ones you listed are not linear or predictable in any mathematical way, so one formula won't cut it, you'll need several to get all those different percents. BTW, that formula will not give you exactly the right amount, you'd need to add a rounding. IE: $7.50 cost equals a percent of 43.5, not 44 even. So: =IF(A1<=39.99,ROUND(-A1/100+0.50+1/100,2),0.12) However, unless you want a long series of IF statements for a formula, why not use a lookup table? Put the table on a separate worksheet, out of the way of your invoice. Or use the "table within a formula" as shown in Gord's link, but in a LOOKUP function: =LOOKUP(A1,{1;2;3;4,etc},{0.5;0.49;0.48;0.47,etc}) For an actual table: You can just enter the table like you have typed here (you would need to take out the ranges, just use $1,2,3...), and use the Index/Match formula I gave for a lookup. Although I designed it with the highest cost number at the top. So: 40.......12% 35.......14% 30.......16% etc |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing - Sliding Scale Price Markup
Thank you for all your help.
I actually would like to exact percentage (not rounded off). I should've made it more clear. Yes, there are 24 percent groups. What would be the formula for a long series of IF statements? Also, is there a limitation to the number of IF statements that you can include within a formula (24 percent groups). Thank you. Ed. "Spiky" wrote in message ... On Sep 1, 2:42 pm, "Ed" wrote: I found the following formula, using interpolation. It will start by assigning a 50% markup to $1.00 and the percentage will go down from there until it reaches $39.99; and will start at 12% for anything above $40.00. =IF(A1<=39.99,(-A1/100+0.50+1/100),0.12) I would like to know how I can add to the formula so that it starts and ends at 14 different intervals: Did you mean 24 percent groups since you listed 24? The ones you listed are not linear or predictable in any mathematical way, so one formula won't cut it, you'll need several to get all those different percents. BTW, that formula will not give you exactly the right amount, you'd need to add a rounding. IE: $7.50 cost equals a percent of 43.5, not 44 even. So: =IF(A1<=39.99,ROUND(-A1/100+0.50+1/100,2),0.12) However, unless you want a long series of IF statements for a formula, why not use a lookup table? Put the table on a separate worksheet, out of the way of your invoice. Or use the "table within a formula" as shown in Gord's link, but in a LOOKUP function: =LOOKUP(A1,{1;2;3;4,etc},{0.5;0.49;0.48;0.47,etc}) For an actual table: You can just enter the table like you have typed here (you would need to take out the ranges, just use $1,2,3...), and use the Index/Match formula I gave for a lookup. Although I designed it with the highest cost number at the top. So: 40.......12% 35.......14% 30.......16% etc |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing - Sliding Scale Price Markup
On Sep 2, 12:47 pm, "Ed" wrote:
Thank you for all your help. I actually would like to exact percentage (not rounded off). I should've made it more clear. Yes, there are 24 percent groups. What would be the formula for a long series of IF statements? Also, is there a limitation to the number of IF statements that you can include within a formula (24 percent groups). Thank you. Ed. "Spiky" wrote in message ... On Sep 1, 2:42 pm, "Ed" wrote: I found the following formula, using interpolation. It will start by assigning a 50% markup to $1.00 and the percentage will go down from there until it reaches $39.99; and will start at 12% for anything above $40.00. =IF(A1<=39.99,(-A1/100+0.50+1/100),0.12) I would like to know how I can add to the formula so that it starts and ends at 14 different intervals: Did you mean 24 percent groups since you listed 24? The ones you listed are not linear or predictable in any mathematical way, so one formula won't cut it, you'll need several to get all those different percents. BTW, that formula will not give you exactly the right amount, you'd need to add a rounding. IE: $7.50 cost equals a percent of 43.5, not 44 even. So: =IF(A1<=39.99,ROUND(-A1/100+0.50+1/100,2),0.12) However, unless you want a long series of IF statements for a formula, why not use a lookup table? Put the table on a separate worksheet, out of the way of your invoice. Or use the "table within a formula" as shown in Gord's link, but in a LOOKUP function: =LOOKUP(A1,{1;2;3;4,etc},{0.5;0.49;0.48;0.47,etc}) For an actual table: You can just enter the table like you have typed here (you would need to take out the ranges, just use $1,2,3...), and use the Index/Match formula I gave for a lookup. Although I designed it with the highest cost number at the top. So: 40.......12% 35.......14% 30.......16% etc You can have only 7 nested IF statements, I believe. You don't actually need 24, though. In looking at your list, I see you can use your above formula for everything from $1.00-15.99. $16.00-18.99 is also a steady range, so if you can alter your calculation to jump 2% each $1, you should be able to stay within the 7 IF requirement. Continuing like this, I see 5 sections. Something like this: =IF(A1<16,(-A1/100+0.51),IF(A1<19,(2nd calc),IF(A1<25,(3rd calc),IF(A1<40,(4th calc),0.12) Sorry, my brain isn't working well on figuring out those formulas offhand. Calculate those formulas for each section and plug them in. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pricing - Sliding Scale Price Markup
Thank you again for all your help.
Ed. "Spiky" wrote in message ... On Sep 2, 12:47 pm, "Ed" wrote: Thank you for all your help. I actually would like to exact percentage (not rounded off). I should've made it more clear. Yes, there are 24 percent groups. What would be the formula for a long series of IF statements? Also, is there a limitation to the number of IF statements that you can include within a formula (24 percent groups). Thank you. Ed. "Spiky" wrote in message ... On Sep 1, 2:42 pm, "Ed" wrote: I found the following formula, using interpolation. It will start by assigning a 50% markup to $1.00 and the percentage will go down from there until it reaches $39.99; and will start at 12% for anything above $40.00. =IF(A1<=39.99,(-A1/100+0.50+1/100),0.12) I would like to know how I can add to the formula so that it starts and ends at 14 different intervals: Did you mean 24 percent groups since you listed 24? The ones you listed are not linear or predictable in any mathematical way, so one formula won't cut it, you'll need several to get all those different percents. BTW, that formula will not give you exactly the right amount, you'd need to add a rounding. IE: $7.50 cost equals a percent of 43.5, not 44 even. So: =IF(A1<=39.99,ROUND(-A1/100+0.50+1/100,2),0.12) However, unless you want a long series of IF statements for a formula, why not use a lookup table? Put the table on a separate worksheet, out of the way of your invoice. Or use the "table within a formula" as shown in Gord's link, but in a LOOKUP function: =LOOKUP(A1,{1;2;3;4,etc},{0.5;0.49;0.48;0.47,etc}) For an actual table: You can just enter the table like you have typed here (you would need to take out the ranges, just use $1,2,3...), and use the Index/Match formula I gave for a lookup. Although I designed it with the highest cost number at the top. So: 40.......12% 35.......14% 30.......16% etc You can have only 7 nested IF statements, I believe. You don't actually need 24, though. In looking at your list, I see you can use your above formula for everything from $1.00-15.99. $16.00-18.99 is also a steady range, so if you can alter your calculation to jump 2% each $1, you should be able to stay within the 7 IF requirement. Continuing like this, I see 5 sections. Something like this: =IF(A1<16,(-A1/100+0.51),IF(A1<19,(2nd calc),IF(A1<25,(3rd calc),IF(A1<40,(4th calc),0.12) Sorry, my brain isn't working well on figuring out those formulas offhand. Calculate those formulas for each section and plug them in. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula for sliding scale percentage?????? | New Users to Excel | |||
Sliding scale Question | Excel Worksheet Functions | |||
Calculating a sliding scale | Excel Worksheet Functions | |||
Sliding Scale Percentage via IF? | New Users to Excel | |||
Percentage Commission on a sliding scale. | Excel Discussion (Misc queries) |