Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Average with out zeros or blanks
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. |
#2
|
|||
|
|||
Quote:
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
__________________
Thanks Bala |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Multiple Worksheets without blanks or zeros | Excel Discussion (Misc queries) | |||
Counting blanks as zeros | Excel Worksheet Functions | |||
Average not including Zeros/Blanks | Excel Worksheet Functions | |||
Vlookup blanks = zeros | Excel Discussion (Misc queries) | |||
Blanks chart as zeros | Charts and Charting in Excel |