You're right Dave, I didn't allow for negatives.

So if the remaining cells are all blanks
=AVERAGE(A1:A12)

if the remaining cells are all zeroes
=SUM(A1:A12)/COUNTIF(A1:A12,"<0")

if the remaining cells are a mixture of blanks and zeroes
Then you will need Dave's array formula.


Regards
Martin

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

"MartinW" wrote in message
...
Hi Treesy,

The average function will ignore blanks but not zero values.
So if the formula in those cells is returning 0 alter it to return ""

Alternatively this formula may help.
=SUM(A1:A12)/COUNTIF(A1:A12,"0")
adjust the ranges to suit.

HTH
Martin

"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??