Intentional Circular formula

Hi guys!

I think this is a simple thing I just don't know the vocab to search for it.

Id like to have a formula such as X/Y=Z in which I can replace any number in that formula and have excel calculate the other variables. For instance, if I want to calculate percent profit on a project given a certain fee and expenses but neither the fee, expenses nor profit is an absolute number.

So in essence, three cells and I can change any number in any cell and it will recalculate the other cells based on the value changed.

any help would be greatly appreciated.

Sincerely,

Unfortunately, I have no idea what a VBA solution is and when I search it, it seems more complicated than Trumps tax form.

I'm willing to learn if it gets me to a working solution though.

If you want to use formulas, you can create a helper column with the following formulas:

C1 =IF(B1="",B2*B3,"")
C2 =IF(B2="",B1/B3,"")
C3 =IF(B3="",B1/B2,"")

You can input the two known values for X, Y, and Z into cells B1, B2, and B3 respectively.
You would input 2 values into column B and the third value would show up in column C.

Originally Posted by ChemistB

Let's say, as proposed by Falcondude, B1 = x =b2*b3, B2= y = b1/B3 and B3 = z = b1/b2

If initially, the 3 cells contain 3,2, and 1.5 respectively and you change B3 to 3, there are an infinite number of possibilities for B1 and B2
(1.5, 0.5), (3,1), (6,2) etc. What would you expect in B1 and B2?

I didn't catch that. Good point.

I assumed that the OP would only be solving for one variable at a time.

If initially, the 3 cells contain 3,2, and 1.5 respectively and you change B3 to 3, there are an infinite number of possibilities for B1 and B2
(1.5, 0.5), (3,1), (6,2) etc. What would you expect in B1 and B2?

ChemistB, Great point, I hadn't thought of that either. I just want it to update responsively to an update on any variable. For example: I write a proposal for $20,000 and expect my expenses (including 3rd party consultants) to be $18,000. I can see that I have a profit of 10%. If I want to update it so that my profit margin is 12%, I can update that and it will recalc my proposal value. The perhaps consultant prices come back higher than expected. How does that impact my profit and how would I have to adjust my proposal to even it out? I know this is all very simple math; it just seems like there should be an equally simple formula to incorporate into my proposal template that can account for this kind of flexure. Am I just overthinking this?

Glenn Kennedy, I don't use VBA solutions either. If we could only figure these out. . .

Originally Posted by TreeToes83

ChemistB, Great point, I hadn't thought of that either. I just want it to update responsively to an update on any variable. For example: I write a proposal for $20,000 and expect my expenses (including 3rd party consultants) to be $18,000. I can see that I have a profit of 10%. If I want to update it so that my profit margin is 12%, I can update that and it will recalc my proposal value. The perhaps consultant prices come back higher than expected. How does that impact my profit and how would I have to adjust my proposal to even it out? I know this is all very simple math; it just seems like there should be an equally simple formula to incorporate into my proposal template that can account for this kind of flexure. Am I just overthinking this?

Glenn Kennedy, I don't use VBA solutions either. If we could only figure these out. . .

It sounds like you are only expecting to solve for one variable at a time, not multiple like you suggested in your original post.

You can use the same logic used with the formulas suggested in post #5 to solve for one variable at a time.