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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top