Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
skip cells with zero values in chart (cells not empty) | Charts and Charting in Excel | |||
I want the autofill/drag function to skip empty cells | Excel Discussion (Misc queries) | |||
How do I get an average for 5 when I need to skip cells? | Excel Discussion (Misc queries) | |||
How to skip empty cells while running a macro? | Excel Programming | |||
Skip empty and text cells | Excel Programming |