I have a created a variable with a loop, how do I find average ofvariable most efficiently?

[PeterFauler]

I want to take the average of score without displaying any numbers in
excel and having to paste into excel a formula. i am looking for
assistance to some how average the variable and then paste the answer

Sub rndgenandaver()
Dim score, t, lastt
t = 1
lastt = 10
ReDim score(lastt)
t = 1
For t = 1 To lastt
If t > 0 Then score(t) = Rnd()
Next t
End Sub

 

[Tom Ogilvy]

Sub rndgenandaver()
Dim score, t, lastt
t = 1
lastt = 10
ReDim score(1 to lastt)
t = 1
For t = 1 To lastt
If t > 0 Then score(t) = Rnd()
Next t
msgbox Application.Average(score)
End Sub

worked for me.

 

[PeterFauler]

Thanks for your reply. That was helpful but I am looking for a way to
have a variable such as averagescore hold the value so I can use it in
other places and not just pop up in a message box.

 

[StumpedAgain]

Just use what Tom posted. You don't have to use it in a message box. If you
define averagescore as integer you can then just say:

averagescore = application.average(score)

You can even skipp the assigning part and say

Range("A1").Value = Application.Average(score)

 

[PeterFauler]

that was defintiely helpful, how would you change it if you added a
second variable. look below at the example. it is incorrectly taking
scores from previous weeks. is there a way to clear the variable, once
it is captured


Sub rndgenandaver()
Dim score, t, lastt, averagescore, n, numberofweeks
t = 1
lastt = 16
n = 1
numberofweeks = 16
ReDim score(lastt, numberofweeks), averagescore(numberofweeks)
n = 1
For n = 1 To numberofweeks
t = 1
For t = 1 To lastt
If t > 0 Then score(t, n) = Rnd()
Worksheets("Sheet1").Cells(t, n) = score(t, n)
Next t
averagescore(n) = Application.Average(score)
Worksheets("Sheet1").Cells(112, n) = averagescore(n)
Next n
End Sub

 

[Per Jessen]

that was defintiely helpful, how would you change it if you added a
second variable. look below at the example. it is incorrectly taking
scores from previous weeks. is there a way to clear the variable, once
it is captured

Sub rndgenandaver()
Dim score, t, lastt, averagescore, n, numberofweeks
t = 1
lastt = 16
n = 1
numberofweeks = 16
ReDim score(lastt, numberofweeks), averagescore(numberofweeks)
n = 1
For n = 1 To numberofweeks
t = 1
For t = 1 To lastt
If t > 0 Then score(t, n) = Rnd()
Worksheets("Sheet1").Cells(t, n) = score(t, n)
Next t
averagescore(n) = Application.Average(score)
Worksheets("Sheet1").Cells(112, n) = averagescore(n)
Next n
End Sub

Hi

You don't need a two-dimensional array. Just re-use a one dimensional
array. Try this:

Sub rndgenandaver()
Dim Score()
Dim LastT As Integer
Dim AverageScore()
Dim NumberOfWeeks As Integer
Dim t As Integer
Dim n As Integer

LastT = 16
NumberOfWeeks = 16
ReDim Score(LastT), AverageScore(NumberOfWeeks)

For n = 1 To NumberOfWeeks
For t = 1 To LastT
Score(t) = Rnd()
Worksheets("Sheet1").Cells(t, n) = Score(t)
Next
AverageScore(n) = Application.Average(Score)
Worksheets("Sheet1").Cells(112, n) = AverageScore(n)
Next
End Sub

Best regards,
Per