Solving equation - using different variables

I think the easiest way to do this looks something like this:

1) Decide on a "toggle" input that will tell the spreadsheet which quantity is the unkown. Assuming "leave the cell blank" is the toggle used, I would set it up something like this:

Please Login or Register  to view this content.

I have obviously assumed that the user will be smart enough not to leave 2 cells blank, or not to use empty string "" or 0 to indicate blank.

I find that this is the easiest approach, because it separates inputs and outputs to different cells, since a spreadsheet cannot store both a formula and an input in the same cell. It often seems that users who post this kind of question want (demand) the entire thing (input and output) to occur in the same range of cells. If this is the case for your question, you should know that having both input and output in the same range will require VBA (probably in change and or calculate events) to perform the same task (analyze the input for the "solve for this variable" toggle, perform the computations, and output the result to the range). If you are already skilled at VBA programming and using the change/calculate events, this should be straightforward.

I put together a set of macros that should solve most problems, as long as their results are convergent (if C = 12, but when C = 10 you're farther away from a solution than if C = 7 it is divergent and this solution won't be reliable)

Just put both sides of the equation into the cells E2 and G2 :

ex: A + 2B = C would be input as:

Please Login or Register  to view this content.

Notice the variables are surrounded by underscores. They have to be entered that way simply because that's the way I named them. Go to the name manager if you'd like them to have a different name.

For those who are interested in the code without wanting to dl a file:

NOT THE MOST RECENT VERSION

Please Login or Register  to view this content.

Hope that helps! (PS. I know there are really well built equation solvers online... you might have more luck there...)

I changed the macro a little bit so it won't keep finding additional decimals if it finds a perfect solution:

NOT THE MOST RECENT VERSION

Please Login or Register  to view this content.

Sorry, one more edit. I did some more testing. This version seems to work best.

Please Login or Register  to view this content.

I use MrShorty's suggestion -- e.g., for cost, price, & margin.

Because a numerical algorithm might be needed with your real equations, since you indicate they are much more complicated than the simple linear function you use to represent them, a comment on ThirdFret's proposed solution. It appears that he is using a basic "brute force" kind of root finding algorithm.

When I decide that I need a numerical rather than an algebraic solution to a problem like this, I first look to the built in Goal Seek/Solver utilities that are built into Excel. These utilities use a Newton Raphson type algorithm to find roots of equations. If you set up the spreadsheet correctly (something like Thirdfret has done where you are looking for the place where A+B-C=0), these utilities should be able to find the root(s) of your equations fairly readily. If you are unfamiliar with NR type algorithms, I would suggest this tutorial http://www.cs.utah.edu/~zachary/isp/...ot/Newton.html to understand how the algorithm works, when it has difficulty and when it fails.

If you decide that you need to program your own root finding algorithm like ThirdFret did, I would suggest a bisection or Newton Raphson type algorithm rather than a brute force algorithm.

Properly formulated, there should be no special consideration for a variable on "both" sides of the equal sign. This is because in your "objective function" (the actual function that you have Solver/Goal Seek look at), all variables should be on one side of the equation. For your example, the OF is rearranged to be OF=A+B-B*C (Using the template of your sample sheet, I would probably add a formula =E2-G2). Then, you instruct Solver/goal seek to "Set target cell" containing the OF, "to a value of 0" "by changing" the cell containing B.

edit: I Tried your solution of simply using a third cell to equal one side of the equation subtracted from the other and used the solver to change one variable with a target of zero in that 'difference' cell. That works really well. Thanks for drawing attention to it.