Average formula only works partially.....

[acbel40]

Whew….anyone can help me with this…I’d 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",AVERAGE(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 zero’s…it needs to
have a zero in the total

I hope that ALL makes sense....

 

[T. Valko]

Try this:

=IF(SUM(M6:M17)=0,0,SUMIF(M6:M17,">0")/COUNTIF(M6:M17,">0"))

 

[ryguy7272]

I would do something like this
=IF(ISERROR(AVERAGE(IF($B$2:$B$101<>0,$B$2:$B$101))),"",AVERAGE(IF($B$2:$B$101<>0,$B$2:$B$101)))

Enter as Ctrl+Shift+Enter, not just enter.

Also, check this out:
http://www.ozgrid.com/Excel/average-without-zero.htm

HTH,
Ryan---

 

[Jacob Skaria]

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:M17>0,M6:M17)),0)

If this post helps click Yes

 

[acbel40]

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]

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

 

[acbel40]

I apologize...it DID work....!!! THANK YOU very much!!!