PDA

View Full Version : Maximization of Value by Changing Parameters



bflorez
11-16-2005, 08:46 AM
Hi VBA Guys,

I am trying to maximize the value in a cell that is dependent upon a number of other cells, which are - in turn - dependent upon other cells (both by simple mathematical relations and by if() and countif() logic). I have 5 parameters that I can vary, and I am trying to determine how to optimize the value of my objective function by changing these 5 parameters.

I have attached the spreadsheet if you think you can handle it! ;)

Thanks a lot,
Brendan

BlueCactus
11-16-2005, 11:10 PM
Before you go into VBA, have you tried the Solver (Tools menu) for this problem?

bflorez
11-17-2005, 06:26 AM
Before you go into VBA, have you tried the Solver (Tools menu) for this problem?

Good thought, but I've defintely tried it. The problem is that due to the use of the IF and COUNTIF() logic, the relationship between variables in highly non-linear, so Solver doesn't do a thing.

I think what is needed is some sort of Monte Carlo simulation where a bunch of values are taken over the range of the 5 input variables, to create something of a 5-dimensional space, then based upon some sort of gradient or something, to try to (at least get close to) maximizing the energy function.

Thanks,
Brendan

BlueCactus
11-17-2005, 07:03 AM
I think what is needed is some sort of Monte Carlo simulation where a bunch of values are taken over the range of the 5 input variables, to create something of a 5-dimensional space, then based upon some sort of gradient or something, to try to (at least get close to) maximizing the energy function.
That's what I was afraid of. :rofl: Sometimes Solver can deal with some pretty complex problems. Other times it chokes on the simplest things. I've got enough computational problems of my own to deal with, so good luck with this one! :thumb

mdmackillop
11-17-2005, 03:48 PM
Hi Brendan,
I've no idea how to do this, but I'm willing to "give it a go".
2 questions
Confirm which cells contain your 5 parameters?
Have you an estimate of what your desired result would be?
Regards
MD

BlueCactus
11-17-2005, 08:33 PM
Hi Brendan,
I've no idea how to do this, but I'm willing to "give it a go".
2 questions
Confirm which cells contain your 5 parameters?
Have you an estimate of what your desired result would be?
Regards
MD
A quick visualization is to imagine the five parameters as giving rise to a five-dimensional surface, upon which you are searching for the minimum (or maximum). The big problem is trying to find the true minimum without either a) plotting the entire thing, or b) getting stuck in some local valley since you don't know what's just over the hill.