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