Excel Solver: Solving based on an average
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 开发者_C百科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.
精彩评论