Are you assuming there are no negative numbers?

Actually, yes I am assuming that. In practical terms, it wouldn't make sense
to average both positive and negative numbers and omit zero.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Dave Thomas" wrote in message
t...
Are you assuming there are no negative numbers? Treesy doesn't state that
there aren't. Treesy just wanted to eliminate 0's and blanks.

"Chip Pearson" wrote in message
...
Treesy,

I use that exact need as the example on my Array Formulas web page. The
formula you want is

=AVERAGE(IF(A1:A120,A1:A12,FALSE))

Since this is an array formula, you must press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit it
later. If you do this properly, Excel will display the formula enclosed
in curly braces { }.

Change the range A1:A12 to the range containing your data.

See www.cpearson.com/Excel/ArrayFormulas.aspx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Treesy" wrote in message
...
Is there a way to not have a blank or zero value not be counted in an
average
function. Example:

I have a spreadsheet that has a year's worth of data. Currently, it is
only
filled in through June. At the very end, I want it to average some of
the
data. The formula has 12 cells that I want averaged but if one of those
cells is blank or zero, I want it ignored. 6 of the 12 cells have data.
Right now, I want it to average the 6 cells that have data. Next month,
it
will average 7, so on and so forth. Right now, it is taking the 6
values and
dividing by the 12 cells, even though 6 of them contain nothing. I
guess I
want the calculation to change as data is entered without having to
change
the formula every month and add the new cell. Is this possible??