or use the array
=AVERAGE(IF(G:G0,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.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in message
...
Hi,

One way is

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

or use the array


=AVERAGE(IF(G:G0,G:G,""))

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



--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"c. murphy" wrote:

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