Average formula works but....

[acbel40]

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....I tried to change the formula to
=IF(ISERROR(AVERAGE(IF(M6:M17<>0, M6:M17))),"0",AVERAGE(IF(M6:M17<>0,
M6:M17))) inserting the 0 between the quotation marks; however, 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? Help?

 

[barry houdini]

Your formula is an array formula. You need to confirm wit
CTRL+SHIFT+ENTER so that curly braces appear automatically around th
formula in the formula bar. Have you done that?

Does this formula give the expected result

=SUM(M6:M17

 

[Francis]

I get 0 using your formula if any one of the cell's value consist a 0 or blank
=IF(ISERROR(AVERAGE(IF(M6:M17<>0, M6:M17))),"",AVERAGE(IF(M6:M17<>0,
M6:M17)))

Not sure I understand your requirement very well but try changing it to
=IF(ISERROR(AVERAGE(IF(M6:M17<>0, M6:M17))),0,AVERAGE(IF(M6:M17<>0,
M6:M17)))
--
Hope this help

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

 

[T. Valko]

column totals with whole numbers end up with

a zero as the total.

Not sure what that means.

=IF(ISERROR(AVERAGE(IF(M6:M17<>0,
M6:M17))),"0",AVERAGE(IF(M6:M17<>0,M6:M17)))

When you quote numbers like "0" then Excel treats that as a TEXT string.
Remove the quotes so Excel treats it as a numer:

=IF(ISERROR(AVERAGE(IF(M6:M17<>0, M6:M17))),0,AVERAGE(IF(M6:M17<>0,M6:M17)))

Or:

=LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(M6:M17<>0,M6:M17))))

Both formula are array entered.

 

[acbel40]

Darn that didn't work either..

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

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

This is the formula I'm using
=IF(ISERROR(AVERAGE(IF(B2:B13<>0, B2:B13))),"",AVERAGE(IF(B2:B13<>0,
B2:B13)))Giving a blank because it returns a #div/0 on Project 2

If I put 0 or "0"…it works for Project 2 (the zero column),
but changes the answer Project 1 to 0

Then because Project 2 is blank…it screws up the formula
I use to calculate the Quarterly #'s.

Because those 0's will be input with whole numbers eventually,
the formula needs to work for all columns (25 columns).

I need to divide the sum of those 12 cells 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.

I hope that ALL makes sense....

 

[barry houdini]

Hello acbel40

Your formula should return the result you expect but it needs to b
confirmed with CTRL+SHIFT+ENTER so that curly braces appear around th
formula in the formula bar. Probably when you changed the "" to 0 yo
didn't do that....

An alternative, assuming you don't have any negative values, whic
doesn't require "array entering"

=SUM(B2:B13)/MAX(1,COUNTIF(B2:B13,">0")