Numeric Solver

**Jacques Grobler** · 01-05-2011, 07:53 AM

Hi Guys,

I have a bit of an interesting request. I have been thinking of how to program a procedure but I can not effectively think of how to write it?

I need to be able to feed an equation into a procedure and have the code determine the unknown variable, whether it is on the left hand side of the equal or the right hand side.

Example:
A + B = C / D
Solve B?

The above is still a very simple example.
And I need to be able to feed any equation into the solver and obtain any variable form that equation.

**shg** · 01-05-2011, 08:50 AM

That's kind of what Mathematica, Maple, and MatLab do for a living.

**snb** · 01-05-2011, 09:50 AM

Sub example()
  MsgBox snb(4, 5, 8)
End Sub

Function snb(a, c, d)
  snb = c / d - a
End Function

**squiggler47** · 01-05-2011, 10:04 AM

Yes, MathCad is good at this! Writing your own is a none trivial exercise!

There may be Open Source versions out there if you search!

I use MathCad to optimize my formulas sometimes, rather than hand simplify them, but it still has shortcomings!

The difficulty of this subject is apparent by the lack of software out there, I have often thought it would be nice to SOLVE excel formulas, with simplification and bracket removal and term collection, but alas nothing is out there!

**Jacques Grobler** · 01-07-2011, 06:59 AM

Hi Guys,

I’ve had a look at all the suggestions, thanks.

Shg:
I’d like not to have a standalone application for this. The proposed packages cost money which I’d prefer not to spend.

Snb:
The idea behind the exercise is to not need to manipulate the formulas in order to obtain the unknown values.

Squiggler:
I will have a look at MathCad, also had a look for Open Source products but for some stupid reason I cannot install .oxt files on my pc, doesn’t recognise the file extension. Tried to download an application or two and again, all everyone want is money.....!

I’d like to use my proposed program as a type of addin, then you can simply, in any sheet, enter a formula, add the values and determine the unknown... Instead of opening another program, enter the formula, blah, blah, blah, blah... It basically runs at real time then.

Squiggler, if you’d also like a program like this, why don’t you and I put our heads together and see what we can come up with? I have an idea but the problem with giving you my idea now, is that you might fall in the same rut of thinking as me. I have actually spent the last two nights working on a program but it is VERY MUCH far from complete and has MANY short comings... But I think it might just go somewhere. Please let me know what you think.

**squiggler47** · 01-07-2011, 08:25 AM

I've had 30 years programming experience, and I know the pitfalls, you could solve individuals but the logic for solving a variety is complex!

I already have MathCad and it serves my purpose, most of the time its as easy to use pen and paper to do the work!

.oxt is not an extension I recognise, so no clue what you downloaded!

This is worth a look, but they are often not too simple to use!

http://maxima.sourceforge.net/

Thats why I use MathCad its fairly easy to get started!

**squiggler47** · 01-08-2011, 07:21 AM

Taking your example

A + B = C / D

and solving for B taking the assumption that variables would be in a range, you have to apply the laws of maths which in this case is easy, by paper just change A to -A and add to the end of the right hand side +B then is B so all is fine, you can then replace A C D with the cell references and use it directly as the formula in the cell, not too complicated

Its when you start thinking about brackets and division and multiplication, you have to figure when changing sides you need to add brackets to whats there to make the sums work correctly etc, what happens when you have something like A+B = (C+D)^B how then to solve for B?

I know that Mathcad and Maple etc are expensive, but the reason behind that is the complexity of the problem!

**Jacques Grobler** · 01-19-2011, 01:26 AM

Morning Darren,

The laws of maths are pretty clear to me as I am a Mechanical Eng. And yes, the example I showed was very simple.

The example that you indicated, can be achieved by itteration.

Attached is what I have in mind. My problem is that sometimes it works, other times is doesn't. And I don't know if the way I do my itteration might be the reason for this.

The program works as follow:

1> The user enters a formulae into 2 text boxes, left and right side of the equation. These formulae are then pasted into two cells in excel and given names.
2> The user then enters the name of the known variables as well as their values. These are also pasted into cells and given names.
3> The user lastly enters the name of the unknown variable and start the solving process.

In the background, starting from a base value of say 0.0000000001. This value is entered into the cell where the unknown variable would be filled in in order to balance the equation. If the equation is not balanced within say 1%, the sign is changed to -0.0000000001 and is repeated. This value is then incremented by 0.0000000001 and sign changed and repeated to the point where the difference between the left and right side of the equation differs by less than 1%, this is then the value of the unknown variable.

**Jacques Grobler** · 07-18-2011, 02:10 AM

Morning all,

So the next step in my evolution of my numeric solver was achieved by incorporationg the installed add-in "Solver" into my code. This is now able to calculate any formula I enter left and right of the "=" sign and specify the variables and the unknown (see the attached for the program).

However, the next trick comes into play as follow:
I do not want to use any excel cells for ranges or values, I want everything to happen in the background of excel, all in vba. The reason for this is that I have a library running in the form of an add-in containing all my formulae. Currently, if I have 3 variables, I have to do my code containing 3 IF statements, 10 variables, 10 IF's.... You get the point. However, if I can get the solver to calculate this, I only need to specify the folrmula for left and right, the varialbes and values and then obtain the unknown.

So the big question is: Has anyone come accross something like this before? Or am I the only crazy one thinking this is possible? :D

Your assistance will be greatly appreciated.

**Jacques Grobler** · 07-18-2011, 09:28 AM

No takers on this one? :-(

**mikerickson** · 07-18-2011, 09:47 AM

1> The user enters a formulae into 2 text boxes, left and right side of the equation. These formulae are then pasted into two cells in excel and given names.

Simple enough.

2> The user then enters the name of the known variables as well as their values. These are also pasted into cells and given names.

Not difficult, using SUBSTITUTE.

3> The user lastly enters the name of the unknown variable and start the solving process.

No need for the user to enter the unknown name, but it helps.

At that point the solving process is pretty straight forward. Convert the formulas into a single difference equation and use a binary search to find a zero.

But I note that you are looking for accuracy better than 1e-10. (The last paragraph talks of starting at 1e-10 and then improving.) Excel is not the platform for that kind of accuracy. With this kind of problem, its common (in Excel) to assume 1e-8=0.

**Jacques Grobler** · 07-19-2011, 12:43 AM

I have managed to use the solver add-in for solving values in excel, thus this is no more a problem.

Right, let's assume I'm not going for that degree of accuracy, but rather what excel can achieve. What you describe to me has to be done in the excel interface, I'm trying to have all this happen in vba only, passing functional formulas to the solver to obtain the unknown.

Is this possible?

**Jacques Grobler** · 07-19-2011, 08:06 AM

No one up for this one?

**MrShorty** · 07-19-2011, 10:36 AM

I'm trying to have all this happen in vba only, passing functional formulas to the solver to obtain the unknown.

Is this possible?

I don't think it is. I recall researching this issue once, and the conclusion I came to was that the Solver Add-in was programmed to work within a spreadsheet only, and couldn't be accessed directly by VBA (unless this has changed in later versions of Excel). I might have dreamed it, but I think I remember coming across a retail, stand-alone version of Solver that could be accessed by different programming languages, if you are interested in researching that option.

The only way I can see to get VBA to talk to solver is to have VBA set up a spreadsheet, then call solver (I don't remember the commands, but they were described in VBA help) for that spreadsheet, then extract the result(s) back into VBA.

If you cannot go through a spreadsheet like that, you could program your own algorithm. In a generic sense, this is called "finding the roots of equations" and gets a lot of air time in a numerical methods text. The first step in a generic root finding algorithm is to get the equation in a form where you are looking for x so that f(x)=0. In the sample equation, subtracting the right from the left to get A+B-C/D=f=0. The routine then needs a way to identify the known quantities and the unknown quantity. Most root finding algorithms will also require an initial guess as a starting point. It seems that most generic root finders will use the Newton Raphson method, though, as mentioned, any generic numerical root finder will have situations where it will converge on the wrong root or fail to find a root at all (you will get the same pitfalls with the Solver add-in because it uses these same numerical methods).

Anyway, if you decide it must be done in VBA, you can do it (these numerical methods are not new, nor are they "magical," it just takes some research and practice to build robust algorithms/programs).

**mikerickson** · 07-19-2011, 03:18 PM

You do not need VBA to get Excel to evaluate a formula.

Consider the attached
A1:A5 hold variable names whose values are in B1:B5

E1 holds the formula to be evaluated.

Names are used. (In this example fourthSub is the highest)
Name: firstSub RefersTo: =SUBSTITUTE(Formula, Sheet1!$A2, Sheet1!$B2)
Name: secondSub RefersTo: =SUBSTITUTE(firstSub, Sheet1!$A3, Sheet1!$B3)
...
Name: SubstituteAll RefersTo: =fourthSub
Name: FormulaValue RefersTo: =EVALUATE(SubstituteAll)

Cell E2 holds the formula =SubstituteAll and shows the formula with values substituted for variables.
Cell E3 holds the formula =FormulaValue and shows the value of the formula with the variables as indicated.

This kind of set-up should be amenable to analysis with Solver.