Excluding 0's in an average

[eblake]

I have a list of statistics like below, Im currently using =IF(ISERROR(AVERAGE(C13:C43)),"",AVERAGE(C13:C43)) to get the average over the whole month, the problem is that some of those days have 0's in them which messes up the true average.

10.66
0.00
9.29
7.04
5.81
11.55
9.95
0.00
0.00
11.17
12.19
14.97
12.56
7.56
0.00
0.00
8.09
8.01
9.89
7.78
11.92
0.00
0.00
10.42
11.79
14.10
7.11
16.92
0.00
0.00
14.47

Thanks in advance.

 

[nbrcrunch]

http://www.officefrustration.com/q-f_3_p_33.html

http://www.officefrustration.com/q-t_45884-Average-excluding-zero-value-cells.html

and finally,
my quick & dirty method is to count all the cells with value and subtract it from countif zero. The net result is then your divisor

 

[parry]

=SUM(C13:C43)/COUNTIF(C13:C43,">0")

 

[Bubbis Thedog]

A non-condescending observation:

Depending on the situation, you should be very careful to assume in a stat analysis that zeros are insignificant. Those zero values can be 'events' that are just as significant as any other number. I'd see them as outliers most of the time; but sometimes zeros are indeed significant.

Just a thought.

Bubbis

 

[eblake]

The Data

The data is actually my agents stats for each day of the month, I need to exclude the 0's which are their days off.

Thanks for the options, ill give them a try.