Solving an equation with eight variables

Hello,

I have a linear equation with eight variables and when I use my HP calculator i can enter any seven of these variables to then be given the 8th as an answer.

My question is, is this possible in Excel, I have tried searching and using the solver but this restricts me - or so it seems - to one answer as in i need to have seven solvers running.

(((1/(1+(R1%*D)/B1))/(1/(1+(R2*D)/B2))*S)-S)*Coff

Many Thanks

Pete

It has been a long time since I last used one of those fancy HP calculators. As I recall, the equation solver built into the calculator simply used a Newton-Raphson algorithm to find the root(s) of the given equation. Because Solver also uses the NR algorithm, I see no reason why you can't program this in Excel.

I'm not certain at this point what your question really is. What do you mean by it "restricts me to one answer" and "I need to have seven solvers running"? A sample file might be useful for illustrating your current solution to the problem to show where it is not working and what you want it to do so that it works for you.

Amazingly they're still very useful, mine's one of the old 'two-thumb' models which make it look as if i'm playing a PSP!

This is a facility i'm trying to create for another area.

There are 8 variables:

Swap Rate 1 Rate 2 Days Basis 1 Basis 2 Spot Coefficient

Typically the last five are know and then you know at least two of the first three.

I can't seem to upload a file but if you use these variables:


Rate 1 Rate 2 Days Basis 1 Basis 2 Spot Coefficient
5.2 2 30 360 360 1.25 10000

And this formula:

=(((1/(1+(G4%*I4)/J4))/(1/(1+(H4%*I4)/K4))*L4)-L4)*M4

G4 is Rate 1 - I4 is days - J4 is Basis 1 - H4 is Rate 2 - K4 is Basis 2 - L4 is Spot and M4 is Coefficient

This will give you the first answer (Swap) as -33.1895

But in my HP if i plug in all but one of these variables i get the 8th answer i.e. whichever one I leave out.

Does that make sense?

Thanks

But in my HP if i plug in all but one of these variables i get the 8th answer i.e. whichever one I leave out.

So the Solver part works just fine, your question is about identifying the "missing" variable and using that as the "by changing" argument in the Solver routine.

Excel/Solver is not going to have a built in routine for identifying the unkown, you, the programmer, need to be able to supply that information to Solver. At this point, you will need to decide how automated this needs to be, and how much you are going to leave up to the user.

Since I program spreadsheets almost exclusively for my own use, I don't have a problem leaving it up to the user to properly select the "by changing" cell when he/she calls Solver. This is probably the easiest to program, but it does put some responsibility on the user to make that selection correctly. One very real advantage to this kind of approach is that the user can easily select their own "initial guess" for the unkown which will improve the time needed to converge to a solution and allow the user to control which solution is chosen in cases where multiple solutions are possible.

If you don't trust your users to be able to handle this manually, you will need to build a routine into the spreadsheet that will identify the unkown and pass that information to Solver. This is probably easiest using VBA. If you are unfamiliar with VBA, record a macro while you manually set up the Solver model. Then you should be able to edit the resulting code with an additional loop/code block that will check the main input range and identify the "toggle" for the unkown (blank is what HP used, if I remember correctly, you don't need to limit yourself to this.) and pass that cell as the "by changing" argument to the Solver part of the routine. If 0 is not a good initial guess for any of those variables, I would probably add additional code to select a better initial guess to put into the unkown cell before starting the Solver routine.

At this point, you need to decide how much needs to be programmed in up front based on your skill as a programmer and based on the expected users skill in calling Solver.

'Skill as a programmer' - oh my you flatter me sir.

If i gave something that required any input from the users we'd have a melt-down.

Many thanks for your kind reply but I think i will revert to my HP and build them an Excel sheet with 3 or 4 options rather than ask them to use the solver function.

Regards

Pete