how do you skip empty cells or zeros when calculating the average
In my calculation where I find the last row and calculate the average of the
last 30 sets of data, I come across zeros. How do I ignore zeros in VB macros. Ex. 0 (or empty set in some cases) 3 6 9 12 average = 6 in Macro The average should be 7.5 because first value was zero (or nearly zero) which means I'm going to have to do some kind of test that says if the zero value is less than .01, ignore it when calculating the average. |
how do you skip empty cells or zeros when calculating the average
Something like this should do it
Function myAvg(ByVal data) As Double Dim count As Integer, mySum As Double For Each c In data If IsNumeric(c) And c = 1 Then mySum = mySum + c count = count + 1 End If Next myAvg = mySum / count End Function Peter "soccerdav2003" wrote: In my calculation where I find the last row and calculate the average of the last 30 sets of data, I come across zeros. How do I ignore zeros in VB macros. Ex. 0 (or empty set in some cases) 3 6 9 12 average = 6 in Macro The average should be 7.5 because first value was zero (or nearly zero) which means I'm going to have to do some kind of test that says if the zero value is less than .01, ignore it when calculating the average. |
All times are GMT +1. The time now is 03:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com