To average A1, A4, A7, A10, etc and exclude 0 values...

Array entered** :

=AVERAGE(IF((MOD(ROW(A1:A100)-ROW(A1),3)=0)*(A1:A1000),A1:A100))

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

--
Biff
Microsoft Excel MVP


"Excel-User-RR" wrote in message
...
Hi, Thanks for the reply - I forgot to mention that the reason why I was
looking for a formula for non-consecutive cells is because some rows have
values that I do not want to include in the average (I also forgot to
number
the blank rows; I corrected this in the example below). If It helps, I
only
want to average selected cells (e.g.-A1, A4, A7,...) excluding any zero
values. Is there a way to do that with a formula? Thanks again.

"Teethless mama" wrote:

=SUM(A1:A8)/COUNTIF(A1:A8,"0") normally entered
or
=AVERAGE(IF(A1:A80,A1:A8)) ctrl+shift+enter, not just enter

"Excel-User-RR" wrote:

Can someone please help with construction of a formula to find an
average for
cells that are in the same column but are not in the same range and may
include zero values & blank rows? I am looking for something that will
average only the values that are greater than zero. The cells with zero
values will vary and will be separated by blank rows. For instance I
would
like to average column A cells in rows 1, 3, & 5 for a result of 7.
Thanks.
For example see below:

A B C
1 6 9 2
2 0 1 4
3***Blank Row***
4 8 0 0
5 5 2 12
6***Blank Row***
7 0 8 10
8 3 0 11