Average non-consecutive cells excluding zero

[Excel-User-RR]

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

 

[Teethless mama]

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

 

[Rick Rothstein]

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?

 

[Excel-User-RR]

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.

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

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

 

[Rick Rothstein]

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

 

[T. Valko]

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

Array entered** :

=AVERAGE(IF((MOD(ROW(A1:A100)-ROW(A1),3)=0)*(A1:A100>0),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.

 

[Rick Rothstein]

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

 

[Excel-User-RR]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!