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

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

--
Biff
Microsoft Excel MVP


"c. murphy" <c. (E-Mail Removed)> wrote in message
news:277CC267-E549-4794-A610-(E-Mail Removed)...
>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

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.



--
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

>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.

--
Biff
Microsoft Excel MVP


"Shane Devenshire" <(E-Mail Removed)> wrote in message
news:A5B5B66E-8AD1-4C01-ABBE-(E-Mail Removed)...
> 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.
>
>
>
> --
> 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

>=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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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"))
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "c. murphy" <c. (E-Mail Removed)> wrote in message
> news:277CC267-E549-4794-A610-(E-Mail Removed)...
>>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
>
>