I have a parameter in A1 that influences "TOTAL" in a random and very high standard deviation. Lets say A1 is 2...then TOTAL Values could be 1...5...17...3...2..2...etc If A1 is 1 then TOTAL Values could be 1....3...5..15...9...10..etc
I would like solver to figure out which value in A1 would equate to the best AVERAGE of TOTAL after X runs. Where I can define X.
In my example you can tell that
A1=1 is better on average after 6 runs. However, if you run solver normally it would say
A1=2 is the best, because it produced a value of 17.
This doesn't seem to be the kind of problem you solve with solver. Why not write a macro that loops through the values of A1, X times, keeping a running sum of the TOTAL values for each A1? When it's all over, the largest sum is also the largest average.
The inner loop will be something like this:
Redim tSum(1 to maxA1) for i = 1 to maxA1 tSum(i) = 0 for j = 1 to X [A1] = i Application.calculate tSum(i) = tSum(i) + TOTAL next j next i 'now step through tSum. The index of the largest value ' is the value of A1 desired. Put it in a handy cell.
It has to be a macro, not a function because it changes A1.