How to omit zero values when calculating averages?

[jason-w]

Hi,

I'd like to find a method that allows me to calculate an ongoing YTD average that omits any zero values present in the source data range.

Any zero values in the source data are not valid, so the purpose of omitting them serves to avoid my YTD average becoming skewed due to inclusion of the zero values.

Below is an example of the formula I'm using (pls see attached screenshot for further reference).

G23: =AVERAGE(L23)
G24: =AVERAGE($L$23:L24)
G25: Etc.

screenshot - ss.jpg

Thanks for any assistance that can be provided,
Jason

[Jack in the UK]

something like this should do it, edit range to suite

=SUMIF(A1:A1000,">0")/COUNTIF(A1:A1000,">0")

[Jack in the UK]

playing about a CSE formula would be

=AVERAGE(IF($A$1:$A$1000 <>0,$A$1:$A$1000))

so reads { the above } press CTRL+SHIFT+ Enter all at the same time

[NBVC]

If you have 2010, you can use AVERAGEIF

=AVERAGEIF($A$1:$A$1000,"<>0")

[jason-w]

Thanks all - the following worked great for me:

=sum(l23)/countif(l23,">0")
=sum($l$23:l24)/countif($l$23:l24,">0")

NBVC, I do have 2010, so I'll also try that more simplified formula. Thx.

[Jack in the UK]

Hi Jason
Glad You got is sorted and thanks for letting us all know

Good luck
jiuk