while loop for calculating average in vba code

[maakali]

hi,

i would like to take average of a coloum. i tried using for loop but it restricts the number of rows(as i had to define an array). i want xldown command with while loop so that last cell in a coloumn is used. can anyone help please. i am not sure how while code would work as i want to develop the code futher to calulate st deviation etc but if i get the concept right i will be able to develop

thanks

 

[VoG]

I'm not sure that you need to loop. Try

Sub sums()
Dim x As Double
x = WorksheetFunction.Average(Columns("A"))
MsgBox x
End Sub

 

[maakali]

hi buddy,

i think i will need a loop as you can see from this code. how will i calculate average from every row . this code works fine in for loop but i dont know how to do with while or do loop

Function stdDev(arr() As Single)
Dim i As Integer
Dim avg As Single, SumSq As Double, VAR As Single
avg = MEAN(arr)
For i = 1 To UBound(arr)
arr(i) = Sheets("sheet1").Cells(i, 1)
SumSq = SumSq + ((arr(i) - avg) ^ 2)

Next i
stdDev = Sqr(SumSq / (UBound(arr) - 1))
End Function