How do i compute the average value of 100 iterations?

[Guest]

I need to compute the avg of all 100 iteraions. I set the iterations in the
Tool->options->calculation->iteration. Now i need to compute the avg of all
100 iterations. Any help would be greatly appreciatted.
Thanks,
Il

 

[Bernie Deitrick]

Il,

You would need to use VBA to do the actual calc....

HTH,
Bernie
MS Excel MVP

 

[Guest]

Is there an example of something that i can use to do this?
thanks

 

[Bernie Deitrick]

Try something like the sub below, for a formula in cell A1.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim i As Integer
Dim mySum As Double

With Application
.Iteration = True
.MaxIterations = 1
End With

For i = 1 To 100
Range("A1").Calculate
mySum = mySum + Range("A1").Value
Next i

MsgBox "The average is " & mySum / 100

With Application
.Iteration = False
End With

End Sub

 

[Guest]

Thank you very much!!! it helped a lot

Try something like the sub below, for a formula in cell A1.

HTH,
Bernie
MS Excel MVP

Sub Macro1()
Dim i As Integer
Dim mySum As Double

With Application
.Iteration = True
.MaxIterations = 1
End With

For i = 1 To 100
Range("A1").Calculate
mySum = mySum + Range("A1").Value
Next i

MsgBox "The average is " & mySum / 100

With Application
.Iteration = False
End With

End Sub