Getting Past a Circular reference

[ericb1]

I'm close but still no cigar...

Basically I want to have three cells A1 = cost, B1 = margin (as a percent), C1 = sell price
where
A1 = C1(1-B1/100)
B1 = 1-A1/(100*C1)
C1 = A1/1-B1/100

..circular reference right? How can I make this possible and still have the ability to manually input any two cells and the third automatically populate relative to the other two cells. Sometimes I want to enter cost and margin and see sell price, other times I want to enter cost and sell price and see my margin.

i.e.
A1 = 100
B1 = 15%
Then C1 = 117.65

or
A1 = 100
C1 = 117.65
Then C1 = 15%

Thanks!
-E

[stnkynts]

I am a little confused. How is your cost dependent on your sell price? You sell price could be $1 or $100000, that doesn't change how much it cost you. Your two examples have a consistent A1 value. Is this always the case or is there a situation where you would hand enter your margin and sell price and have it calculate a cost?

[AlphaFrog]

If you manually enter two constants, you will overwrite two formulas and no longer have a circular reference.

Also, on the Options menu Calculation tab, if you check Iterations and set it so something like 1, then Excel doesn't warn you about circular references.

I would suggest you just have three separate input cells and three formula cells. The formulas each reference their respective two input cells.

[shg]

Here's a workbook I use for exactly that purpose. Enter at least two pieces of current data (discount, price and margin) and one piece of new information. It requires you to enable macros.

[shg]

I did that in a hurry a few weeks ago because I needed it quickly, but looking back, it's pretty ugly. Here's a cleaned-up version.