In a fresh workbook enter the values in the range M6:M17 and try the
below...I have tested this..

If this post helps click Yes
---------------
Jacob Skaria


"acbel40" wrote:

Didn't work (I entered it as an array formula)....gave 0 answer in the column
that had whole numbers in it....and #value error on the zero column
"Jacob Skaria" wrote:

Please note that this is an array formula. you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

=IF(SUM(M6:M17),AVERAGE(IF(M6:M170,M6:M17)),0)

If this post helps click Yes
---------------
Jacob Skaria


"acbel40" wrote:

Whew€¦.anyone can help me with this€¦Id be truly grateful€¦.

My spreadsheet has several columns that need to averaged on time spent on
that particular project per month. (example)

Project 1 Project 2
Jan 0 0
Feb 0 0
Mar 0 0
Apr 0 0
May 0 0
Jun 0 0
Jul 0 0
Aug 538 0
Sep 593 0
Oct 0 0
Nov 0 0
Dec 0 0

Aver Total 566 Blank s/b 0 Average Total for Year


I'm using this formula =IF(ISERROR(AVERAGE(IF(M6:M17<0,
M6:M17))),"",AVERAGE(IF(M6:M17<0, M6:M17))) on a multiple column
spreadsheet....it works great....however, if one column has all zero's...then
it returns a blank (because it initially returned a #div/0 error)....I tried
to change the formula to
=IF(ISERROR(AVERAGE(IF(M6:M17<0,I6:M17))),"0",AVE RAGE(IF(M6:M17<0,
M6:M17)))
inserting the 0 between the quotation marks; however, the column totals
with whole numbers end up with a zero as the total. Something is not quite
right about the formula...or I need to add more to the formula?

Basically my end result needs to divide the sum of those 12 cells in the
column by 12 €¦IF they ALL have numbers greater than 0. If any of the 12 cells
has a 0€¦then I need to divide the sum of the 12 cells by the number of cells
that actually have a whole number. If the column is all zeros€¦it needs to
have a zero in the total

I hope that ALL makes sense....