# More efficient way to average array element?

I have a 2 dimensional array - write_array(1 to 23, 1 to 7)

When populated slice write_array(2,x) typically look something like this with variation of times and Empty entries

write_array(2,1) = "TIME"
write_array(2,2) = 23.08
write_array(2,3) = 22.58
write_array(2,4) = 23.02
write_array(2,5) = Empty
write_array(2,6) = Empty
write_array(2,7) = 24.02

Where the array element is Empty I want to replace with the average time of the others so Empty would be replaced with (23.08+22.58+23.02+24.02)/4I have the following code which does this

[code]
'average recent time
recenttimecount = 0
rtSum = 0
recenttime = Application.WorksheetFunction.index(write_array, 2, 0)
For rtcount = 2 To UBound(recenttime)
If Not IsEmpty(recenttime(rtcount)) Then
recenttimecount = recenttimecount + 1
rtSum = rtSum + recenttime(rtcount)
End If
Next

recenttimeAvg = rtSum / recenttimecount
For rtcount = 2 To UBound(recenttime)
If IsEmpty(recenttime(rtcount)) Then
write_array(2, rtcount) = recenttimeAvg
End If
Next
[/code]

Is there a more efficient way of doing this without 2 FOR loops?

Thanks,
Os
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi

You can calculate the average directly using the WorksheetFunction class and then iterate thru your array:

Richard

``````Dim averg As Double

averg = Application.WorksheetFunction.Average(Application.WorksheetFunction.Index(write_array,2,0))

For i = 1 to Ubound(write_array,2)

If IsEmpty(write_array(2,i)) Then write_array(2,i) = averg

Next i
``````

Experts Exchange Solution brought to you by