vba - 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 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.