Quote:
Originally Posted by Permdawg
I'm trying to average a set of cells that are not continuous and would like to get an average as long as the cell is not zero or empty. Here are the cells I'm looking to average. Currently I'm using an average option but it will not give me an average unless all five cells are filled
=AVERAGE(E137,E163,E189,E215,E241)
I heard there is a countif and sum option or something like that but I get too many arguments when trying to use it.
|
i wrote user-defined function for you. Syntax is below
=my_average("Column Name",Starting range, ending range)
example: =my_average("A",1,65536)
measures the average in column A from the cell first to end
copy the below vba code into module
'created by Bala Sesharao
Function my_average(columnd As String, start_range As Variant, end_range As Variant)
Dim a, i, k, count_of_occurence As Variant
k = 0
count_of_occurence = 0
For i = start_range To end_range
a = ActiveWorkbook.Sheets("sheet1").Range(columnd & i)
If a < "" Then
k = k + a
count_of_occurence = count_of_occurence + 1
End If
Next i
my_average = k / count_of_occurence
End Function
all the best