Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Avoiding circular reference on formula
Hi,
There is a formula called retail ratio which is Product Sales divided by the sum of Product Sales and Service Sales. or RR = P/(S+P). I am trying to create a "What If" chart which would allow someone to plug in values for the Retail Ratio. The Service Sales are also known. What I am trying to do is to solve for what the Product Sales would be. So RR and S are know but P is not. Since it is both in the numerator and denomiator, I am trying to come up with a formula that solves for this. Here is what my chart looks like. Can someone help? Thanks, Chuck Person Service Sales Product Sales Total Sales RR 100 $20,000.00 40% 200 $30,000.00 40% 300 $40,000.00 40% 400 $50,000.00 40% |
#2
|
|||
|
|||
Hi, Chuck W.;
Not really "plug-in," as I apparently don't remember my Algebra that well, but Excel's Goal Seek gave me these results: Person Service Sales Product Sales Total Sales Ratio 100 $20,000.00 $13,307.80 $33,307.80 40% 200 $30,000.00 $19,990.53 $49,990.53 40% 300 $40,000.00 $26,615.61 $66,615.61 40% 400 $50,000.00 $33,330.27 $83,330.27 40% Total Sales was Service Sales + Product Sales, for Ratio I used Product Sales / Total Sales, and for Goal Seek, I asked Excel to change the value of a "Ratio" cell to .4 by changing the value of the corresponding "Product Sales" cell. I repeated the Goal Seek on the other three "Ratio" cells. Goal Seek is on the Tools menu. Hope this helps. Ian. |
#3
|
|||
|
|||
Hi, Chuck W;
I have noticed that the Goal Seek numbers do not come out exactly correct. For example, the second Product Sales number should have been $20,000, rather than $19,990.53. But perhaps it is close enough for your purpose. Regards, Ian. "Chuck W" wrote: Hi, There is a formula called retail ratio which is Product Sales divided by the sum of Product Sales and Service Sales. or RR = P/(S+P). I am trying to create a "What If" chart which would allow someone to plug in values for the Retail Ratio. The Service Sales are also known. What I am trying to do is to solve for what the Product Sales would be. So RR and S are know but P is not. Since it is both in the numerator and denomiator, I am trying to come up with a formula that solves for this. Here is what my chart looks like. Can someone help? Thanks, Chuck Person Service Sales Product Sales Total Sales RR 100 $20,000.00 40% 200 $30,000.00 40% 300 $40,000.00 40% 400 $50,000.00 40% |
#4
|
|||
|
|||
Hi again,
I just got Goal Seek to behave better. Tools Options Calculation Checked "Iteration," left the maximum iterations at 100 and reduced the maximum change down to 0.000000. Goal Seek results are now: Person Service Sales Product Sales Total Sales Ratio 100 $20,000.00 $13,333.33 $33,333.33 40% 200 $30,000.00 $20,000.00 $50,000.00 40% 300 $40,000.00 $26,666.67 $66,666.67 40% 400 $50,000.00 $33,333.33 $83,333.33 40% That's better. :) Regards, Ian. |
#5
|
|||
|
|||
Chuck,
Why not just rearrange your formula P = (RR * S) / (1 - RR) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Chuck W" wrote in message ... Hi, There is a formula called retail ratio which is Product Sales divided by the sum of Product Sales and Service Sales. or RR = P/(S+P). I am trying to create a "What If" chart which would allow someone to plug in values for the Retail Ratio. The Service Sales are also known. What I am trying to do is to solve for what the Product Sales would be. So RR and S are know but P is not. Since it is both in the numerator and denomiator, I am trying to come up with a formula that solves for this. Here is what my chart looks like. Can someone help? Thanks, Chuck Person Service Sales Product Sales Total Sales RR 100 $20,000.00 40% 200 $30,000.00 40% 300 $40,000.00 40% 400 $50,000.00 40% |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help solve a Circular Reference | Excel Worksheet Functions | |||
Circular reference | Excel Discussion (Misc queries) | |||
Need formula for sheet & cell reference | Excel Worksheet Functions | |||
I there an easy way to find out if any formula reference a cell? | New Users to Excel | |||
Formula to reference column heading | Excel Worksheet Functions |