Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regressive Logarithmic Retail Markup
Hello,
I am having a lot of trouble trying to figure out a way to formulate a spreadsheet to automate my retail markup process. Here is what I am trying to do: Let's say for items costing me $1.00 I would like to have a 500% markup. A $3.00 item, a 300% markup. By the time an item reaches a cost of $50.00 I would like the markup to fall to around 100%. I am guessing this would involve some kind of logarithmic function, I am not sure. I would like to have a spreadsheet that allows me to adjust the properties of this regressive curve by adjusting 1 or 2 cells and but adjusting them arrive at a curve that I like. Could anyone offer a solution to my problem? It would be most appreciated. Best Regards, Fibonacci |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regressive Logarithmic Retail Markup
For an arithmetic approach...
In B9:D12 enter the column titles and the initial numbers... Cost Markup Price $1.00 6.00 $6.00 $2.00 5.00 $10.00 $3.00 4.00 $12.00 In A10 enter "= 2/47" In B13 enter "4" In C13 enter "=C12-$A$10" In D13 enter "=C13*B13" Format the cells. Fill B13:D13 down to row 59 ------------ Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Fibonacci" wrote in message Hello, I am having a lot of trouble trying to figure out a way to formulate a spreadsheet to automate my retail markup process. Here is what I am trying to do: Let's say for items costing me $1.00 I would like to have a 500% markup. A $3.00 item, a 300% markup. By the time an item reaches a cost of $50.00 I would like the markup to fall to around 100%. I am guessing this would involve some kind of logarithmic function, I am not sure. I would like to have a spreadsheet that allows me to adjust the properties of this regressive curve by adjusting 1 or 2 cells and but adjusting them arrive at a curve that I like. Could anyone offer a solution to my problem? It would be most appreciated. Best Regards, Fibonacci |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regressive Logarithmic Retail Markup
Jim Cone wrote: For an arithmetic approach... In B9:D12 enter the column titles and the initial numbers... Cost Markup Price $1.00 6.00 $6.00 $2.00 5.00 $10.00 $3.00 4.00 $12.00 In A10 enter "= 2/47" In B13 enter "4" In C13 enter "=C12-$A$10" In D13 enter "=C13*B13" Format the cells. Fill B13:D13 down to row 59 ------------ Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Thanks very much Jim, that puts me on the right track. "Fibonacci" wrote in message Hello, I am having a lot of trouble trying to figure out a way to formulate a spreadsheet to automate my retail markup process. Here is what I am trying to do: Let's say for items costing me $1.00 I would like to have a 500% markup. A $3.00 item, a 300% markup. By the time an item reaches a cost of $50.00 I would like the markup to fall to around 100%. I am guessing this would involve some kind of logarithmic function, I am not sure. I would like to have a spreadsheet that allows me to adjust the properties of this regressive curve by adjusting 1 or 2 cells and but adjusting them arrive at a curve that I like. Could anyone offer a solution to my problem? It would be most appreciated. Best Regards, Fibonacci |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regressive Logarithmic Retail Markup
Please don't double-post - you have another answer elsewhere.
Pete Fibonacci wrote: Thanks very much Jim, that puts me on the right track. Jim Cone wrote: For an arithmetic approach... In B9:D12 enter the column titles and the initial numbers... Cost Markup Price $1.00 6.00 $6.00 $2.00 5.00 $10.00 $3.00 4.00 $12.00 In A10 enter "= 2/47" In B13 enter "4" In C13 enter "=C12-$A$10" In D13 enter "=C13*B13" Format the cells. Fill B13:D13 down to row 59 ------------ Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Fibonacci" wrote in message Hello, I am having a lot of trouble trying to figure out a way to formulate a spreadsheet to automate my retail markup process. Here is what I am trying to do: Let's say for items costing me $1.00 I would like to have a 500% markup. A $3.00 item, a 300% markup. By the time an item reaches a cost of $50.00 I would like the markup to fall to around 100%. I am guessing this would involve some kind of logarithmic function, I am not sure. I would like to have a spreadsheet that allows me to adjust the properties of this regressive curve by adjusting 1 or 2 cells and but adjusting them arrive at a curve that I like. Could anyone offer a solution to my problem? It would be most appreciated. Best Regards, Fibonacci |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regressive Logarithmic Retail Markup
Perhaps it was not exactly the answer i was looking for but I didn't
want to seem ungratefull for your efforts. But since you feel the urge to play that age-old game of usenet policeman, I offer you this advice: get a life and/or mind your business. Pete_UK wrote: Please don't double-post - you have another answer elsewhere. Pete Fibonacci wrote: Thanks very much Jim, that puts me on the right track. Jim Cone wrote: For an arithmetic approach... In B9:D12 enter the column titles and the initial numbers... Cost Markup Price $1.00 6.00 $6.00 $2.00 5.00 $10.00 $3.00 4.00 $12.00 In A10 enter "= 2/47" In B13 enter "4" In C13 enter "=C12-$A$10" In D13 enter "=C13*B13" Format the cells. Fill B13:D13 down to row 59 ------------ Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Fibonacci" wrote in message Hello, I am having a lot of trouble trying to figure out a way to formulate a spreadsheet to automate my retail markup process. Here is what I am trying to do: Let's say for items costing me $1.00 I would like to have a 500% markup. A $3.00 item, a 300% markup. By the time an item reaches a cost of $50.00 I would like the markup to fall to around 100%. I am guessing this would involve some kind of logarithmic function, I am not sure. I would like to have a spreadsheet that allows me to adjust the properties of this regressive curve by adjusting 1 or 2 cells and but adjusting them arrive at a curve that I like. Could anyone offer a solution to my problem? It would be most appreciated. Best Regards, Fibonacci |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regressive Logarithmic Retail Markup
I am guessing this would involve some kind of logarithmic function,
... $1.00 - 500% ... $3.00 - 300% ...$50.00 - fall to around 100%. Don't know if you would want to go this route. The limit of the Hyperbolic Cotangent function tends towards 1 as the input increases. Perhaps you could adjust it with the decreasing exponential function also. Maybe a custom function similar to this... Function MyFit(x) MyFit = 2.10169200448472 / Exp(5.55641007649304E-02 * x) _ + 0.869379917043942 * Coth(0.297090954276678 * x) MyFit = CSng(MyFit) End Function Function Coth(x) Coth = (Exp(x) + Exp(-x)) / (Exp(x) - Exp(-x)) End Function Some tests... ?MyFit(1) 5 ?MyFit(3) 3 ?MyFit(50) 1 And as the input increases, it very slowly drops below 1. ?MyFit(1000) 0.8693799 -- Dana DeLouis Windows XP & Office 2003 "Fibonacci" wrote in message ups.com... Hello, I am having a lot of trouble trying to figure out a way to formulate a spreadsheet to automate my retail markup process. Here is what I am trying to do: Let's say for items costing me $1.00 I would like to have a 500% markup. A $3.00 item, a 300% markup. By the time an item reaches a cost of $50.00 I would like the markup to fall to around 100%. I am guessing this would involve some kind of logarithmic function, I am not sure. I would like to have a spreadsheet that allows me to adjust the properties of this regressive curve by adjusting 1 or 2 cells and but adjusting them arrive at a curve that I like. Could anyone offer a solution to my problem? It would be most appreciated. Best Regards, Fibonacci |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Regressive Logarithmic Retail Markup
Thanks Dana,
That is exactly what I was hoping for! Best Regards, Fibonacci Dana DeLouis wrote: I am guessing this would involve some kind of logarithmic function, ... $1.00 - 500% ... $3.00 - 300% ...$50.00 - fall to around 100%. Don't know if you would want to go this route. The limit of the Hyperbolic Cotangent function tends towards 1 as the input increases. Perhaps you could adjust it with the decreasing exponential function also. Maybe a custom function similar to this... Function MyFit(x) MyFit = 2.10169200448472 / Exp(5.55641007649304E-02 * x) _ + 0.869379917043942 * Coth(0.297090954276678 * x) MyFit = CSng(MyFit) End Function Function Coth(x) Coth = (Exp(x) + Exp(-x)) / (Exp(x) - Exp(-x)) End Function Some tests... ?MyFit(1) 5 ?MyFit(3) 3 ?MyFit(50) 1 And as the input increases, it very slowly drops below 1. ?MyFit(1000) 0.8693799 -- Dana DeLouis Windows XP & Office 2003 "Fibonacci" wrote in message ups.com... Hello, I am having a lot of trouble trying to figure out a way to formulate a spreadsheet to automate my retail markup process. Here is what I am trying to do: Let's say for items costing me $1.00 I would like to have a 500% markup. A $3.00 item, a 300% markup. By the time an item reaches a cost of $50.00 I would like the markup to fall to around 100%. I am guessing this would involve some kind of logarithmic function, I am not sure. I would like to have a spreadsheet that allows me to adjust the properties of this regressive curve by adjusting 1 or 2 cells and but adjusting them arrive at a curve that I like. Could anyone offer a solution to my problem? It would be most appreciated. Best Regards, Fibonacci |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
markup rates | Excel Discussion (Misc queries) | |||
Adding Regressive Months Totals | Excel Worksheet Functions | |||
Sales markup plus fee | New Users to Excel | |||
Logarithmic Retail Markup Worksheet | Excel Worksheet Functions | |||
Remove html markup | Excel Worksheet Functions |