# Average formula only works partially.....

A

#### 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

#### T. Valko

Try this:

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

R

#### 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---

J

#### 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

A

#### 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

J

#### 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

A

#### acbel40

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