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%

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.

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%

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.

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%