Solving via Formula rather than Solver Tool Pack

Hi Everyone,

I'm incorporating a statistical model to predict customer buying behavior. In this model, please see attached it uses microsoft's Solver tool to solve for Alpha, Beta, Gamma, and Delta. It uses solver to solve for the values in parameter estimation tab. The current objective is set to B6 and it changes the values in B1:B4.

I'd like it to be powered by formulas instead of using solver. Here is explanatory detail on how the model works: http://www.brucehardie.com/notes/010...2011-01-20.pdf

Thank you so much for the help!

I think converting to all formulas is going to be a lot of work. How much work are you ready to put into this?

As a starting point (for exactly what you are asking for), I would suggest you review this thread (post #8 in particular http://www.excelforum.com/tips-and-t...ind-roots.html ) where I have a sample spreadsheet that finds the maxima of 2D polynomials using the NR algorithm. However, extending this to 5D -- with a formula where the "slopes" are going to be fairly complex to work out, is going to a significant amount of effort to program into a spreadsheet using only worksheet formulas.

What is your motivation for this? If I had to guess, I would guess that you are trying to get the Solver algorithm (or program your own algorithm) to run automatically, without user input. I would suggest that, if this is your motivation, it will be easier to call Solver using a worksheet_change or worksheet_calculate event that will trigger Solver automatically. Using this approach, you would still be using the built in Solver utility, you would just be getting Solver to run without user input. Would that be a suitable approach? If it is, let us know where you need help (if you search this site and the rest of the internet, there are several examples of calling Solver from VBA, including event code).

I'm sure I could help, but it isn't the kind of programming I do a lot of, so I would probably just end up referring to others' examples. As I indicated before, this forum and the internet have a lot of examples of calling Solver from VBA -- often as part of a loop to solve several copies of the same problem. Here's one example http://www.excelforum.com/showthread...=1#post4470911 see post #8 by Alf. So, let's start with Alf's code (or any other example you come across that you like better) and see where you get stuck.

This

Please Login or Register  to view this content.

is not correct syntax for the cells() method. The cells() method takes only 2 arguments: a row number and a column number.

From the help file for the Range object (https://msdn.microsoft.com/en-us/lib.../ff838238.aspx ), you can use the cells() method to return multiple cells. Note the example from the help file:

Please Login or Register  to view this content.

Try

Please Login or Register  to view this content.

and see if this works better.

And since you are running Solver in a loop I would recommend you start you solver macro with the line

Please Login or Register  to view this content.

before the "SolverOk" line in order delete previous constraint settings.

Alf

I haven't gone through your spreadsheet thoroughly (how thoroughly do you need us to go through it for you?), but I think I can see the problem. There is something different about the calculation when it is called from column C than column B. My testing steps:

1) Put 1627 into B1.
2) Run Solver on column B
3) Result maximum looks similar to 1628
1627
alpha 0.875877066
beta 0.828953298
gamma 0.802503139
delta 3.217015364
B(alpha,beta) 1.357
B(gamma,delta) 0.466
LL -2374.8

4) Put 1628 into C1 and copy alpha-delta values for 1628 into C2:C5
5) I note that the LL value in C8 is not the same as B8 was when these same values were in B2:B5 -- I have not decomposed the entire calculations, but the calculation string from C2:C5 back to LL in C8 is not the same as it is for column B.

One of the challenges when I come into the middle of a large development project like this is that I don't know all that went into the project, nor do I understand all of the formulas along the way. I do note that column J's formulas have some fixed references to column B where I might have expected to see additional lookup functions -- could it be that simple?

Since column B seems to work correctly, I would be tempted to structure the loop differently. Instead of having several columns that are optimized, use the existing column B, and loop through the different input values in B1

Please Login or Register  to view this content.

If it is an advantage, one possible advantage is that you should not need to modify the Solver model at all throughout the loop, simply change B1 and run the existing Solver model.