Average non-consecutive cells excluding zero
 

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
***Blank Row***
3 8 0 0
4 5 2 12
***Blank Row***
5 0 8 10
6 3 0 11

Average non-consecutive cells excluding zero
 

=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
***Blank Row***
3 8 0 0
4 5 2 12
***Blank Row***
5 0 8 10
6 3 0 11

Average non-consecutive cells excluding zero
 

I'm not sure I understand your question correctly. First, what are those
numbers on the left of your data? They can't be row numbers because they are
sequential and you clearly show blank rows between some of them. When your
later example refers to "rows" 1, 3 and 5... I presume you are referring to
those sequential numbers and not Excel's actual row numbers, right?
Second... the numbers you are averaging are not consecutive... are they
always every other "row" or could there be variations? If there could be
variations, how do we know which "row" numbers to use? If they are always
every other "row", are they always the odd numbered "rows" and do they
always go to the end of your data?

--
Rick (MVP - Excel)


"Excel-User-RR" wrote in message
...
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
***Blank Row***
3 8 0 0
4 5 2 12
***Blank Row***
5 0 8 10
6 3 0 11

Average non-consecutive cells excluding zero
 

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

Average non-consecutive cells excluding zero
 

With a formula? No. Can you use VB code? If so, here is a macro that will
average only the non-zero value in the selected cells (although, since you
appear to want to treat zero cells as if they were blank, then I wonder why
you would be selecting them in the first place)...

Sub AveragePositiveValues()
Dim R As Range
Dim Count As Long
Dim Total As Double
For Each R In Selection
If R.Value 0 Then
Count = Count + 1
Total = Total + R.Value
End If
Next
MsgBox "Average of selected cells: " & Total / Count
End Sub

Instead of showing the average in a MessageBox (as my example does), you can
assign it to a specific cell (just let us know which cell and we will modify
the code to do that).

--
Rick (MVP - Excel)


"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

Average non-consecutive cells excluding zero
 

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

Average non-consecutive cells excluding zero
 

In light of Biff's posting, let me clarify my "No" answer to you... you
can't **select** various cells (as I read your post as indicating you wanted
to do) and then average those with a formula. Biff interpreted your words
"selected cells" as being *specified cells* as opposed to my interpretation
of selecting cells to form a Selection... as Biff showed, if the cells you
wanted to average were at a fixed offset from each other, you could handle
that situation with a formula (as he showed).

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
With a formula? No. Can you use VB code? If so, here is a macro that will
average only the non-zero value in the selected cells (although, since you
appear to want to treat zero cells as if they were blank, then I wonder
why you would be selecting them in the first place)...

Sub AveragePositiveValues()
Dim R As Range
Dim Count As Long
Dim Total As Double
For Each R In Selection
If R.Value 0 Then
Count = Count + 1
Total = Total + R.Value
End If
Next
MsgBox "Average of selected cells: " & Total / Count
End Sub

Instead of showing the average in a MessageBox (as my example does), you
can assign it to a specific cell (just let us know which cell and we will
modify the code to do that).

--
Rick (MVP - Excel)


"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

Average non-consecutive cells excluding zero
 

Thank You! And I apologize for my convoluted explanation of the problem.
Your formula is exactly what I was looking for but was unable to figure out
on my own.

"T. Valko" wrote:

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

Average non-consecutive cells excluding zero
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Excel-User-RR" wrote in message
...
Thank You! And I apologize for my convoluted explanation of the problem.
Your formula is exactly what I was looking for but was unable to figure
out
on my own.

"T. Valko" wrote:

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