I have a spreadsheet wherein I have a col of equations(f1:fx), all using the value in cell e1.
Then I have cell g1, which takes the product of all the cells (f1:fx).

I set solver to find max for cell g1 by changing e1. The problem is, whenever Solver sets a vale for e1, the values in f1:fx never change (there would have to be an intervening VBA "DoEvents" for that to occur) an thus I never can solve for the value in e1 resulting in the max g1. (the reason I have to do this 'is because otherwise I rapidly get an out of memory error as the equation for g1, if I didn't break it down across a bunch of cells f1:fx, would be too long.

How can I use solver to solve this? Thanks.

On 13/02/2014 02:02, RVic wrote:
I have a spreadsheet wherein I have a col of equations(f1:fx), all using the value in cell e1.
Then I have cell g1, which takes the product of all the cells (f1:fx).

I set solver to find max for cell g1 by changing e1. The problem is, whenever Solver sets a vale for e1, the values in f1:fx never change (there would have to be an intervening VBA "DoEvents" for that to occur) an thus I never can solve for the value in e1 resulting in the max g1. (the reason I have to do this 'is because otherwise I rapidly get an out of memory error as the equation for g1, if I didn't break it down across a bunch of cells f1:fx, would be too long.

How can I use solver to solve this? Thanks.

As I regularly use Solver to optimise SUM(f1:fx) and also VBA functions
I cannot quite understand what your problem is. I haven't noticed any
bad behaviour in the Solver routine apart from a bit of numerical
instabliity and a tendency sometimes to latch onto local optima or
pretend it has a solution when started close to the global optimum.

It is much better for my problems than the MINVERSE or LINEST code.

Perhaps if you posted a concrete example you reckon fails it would be
more obvious. The main reason I use Solver is that the statistical
functions like LINEST are deeply flawed for difficult datasets. And it
also makes it easy to do minimum 1-norm fitting not just least squares.

--
Regards,
Martin Brown