Averaging numbers in a column while excluding blank spaces and zer

[c. murphy]

I am using office 2003 and have the numbers 0, 0, 5, 0, 0, 5, 0, 0, 5, 0, 0,
5 in column G. How do I average only those blocks that contain a number
greater than zero no matter what blocks in that column might contain a number
greater than zero?

Thank You

 

[T. Valko]

Try one of these:

=IF(COUNTIF(G2:G13,">0"),SUMIF(G2:G13,">0",G2:G13)/COUNTIF(G2:G13,">0"),"")

This is an array formula** :

=IF(COUNTIF(G2:G13,">0"),AVERAGE(IF(G2:G13>0,G2:G13)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

This one will return a 0 if no entries are greater than 0. The other
formulas will return a blank:

=SUM(G2:G13)/MAX(1,COUNTIF(G2:G13,">0"))

 

[Shane Devenshire]

Hi,

One way is

=SUMIF(G:G,">0")/COUNTIF(G:G,">0")

or use the array


=AVERAGE(IF(G:G>0,G:G,""))

to make it an array press Shift+Ctrl+Enter to enter it, not Enter.

 

[T. Valko]

or use the array

=AVERAGE(IF(G:G>0,G:G,""))

That will only work in Excel 2007. In all other versions you can't use
entire columns as range references.

Even if you do have Excel 2007 and if you aren't using all 1,048,576 rows,
referencing the entire column in array formulas is *very* inefficient.

 

[T. Valko]

=IF(COUNTIF(G2:G13,">0"),SUMIF(G2:G13,">0",G2:G13)/COUNTIF(G2:G13,">0"),"")

No need to repeat the range in SUMIF. Can be reduced to:

=IF(COUNTIF(G2:G13,">0"),SUMIF(G2:G13,">0")/COUNTIF(G2:G13,">0"),"")