Averaging numbers in a column while excluding blank spaces and zer
 

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

Averaging numbers in a column while excluding blank spaces and zer
 

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:G130,G2:G1 3)),"")

** 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"))

--
Biff
Microsoft Excel MVP


"c. murphy" <c. wrote in message
...
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

Averaging numbers in a column while excluding blank spaces and zer
 

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

Averaging numbers in a column while excluding blank spaces and zer
 

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

Averaging numbers in a column while excluding blank spaces and zer
 

=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"),"")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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:G130,G2:G1 3)),"")

** 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"))

--
Biff
Microsoft Excel MVP


"c. murphy" <c. wrote in message
...
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