Average formula works but....

I'm using this formula =IF(ISERROR(AVERAGE(IF(M6:M170,
M6:M17))),"",AVERAGE(IF(M6:M170, 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:M170, M6:M17))),"0",AVERAGE(IF(M6:M170,
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?

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

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

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

Thank You

cheers, francis





"acbel40" wrote:

I'm using this formula =IF(ISERROR(AVERAGE(IF(M6:M170,
M6:M17))),"",AVERAGE(IF(M6:M170, 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:M170, M6:M17))),"0",AVERAGE(IF(M6:M170,
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?

column totals with whole numbers end up with
a zero as the total.

Not sure what that means.

=IF(ISERROR(AVERAGE(IF(M6:M170,
M6:M17))),"0",AVERAGE(IF(M6:M170,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:M170, M6:M17))),0,AVERAGE(IF(M6:M170,M6:M17)))

Or:

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

Both formula are array entered.

--
Biff
Microsoft Excel MVP


"acbel40" wrote in message
...
I'm using this formula =IF(ISERROR(AVERAGE(IF(M6:M170,
M6:M17))),"",AVERAGE(IF(M6:M170, 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:M170, M6:M17))),"0",AVERAGE(IF(M6:M170,
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?

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:B130, B2:B13))),"",AVERAGE(IF(B2:B130,
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....



"Francis" wrote:

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

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

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

Thank You

cheers, francis





"acbel40" wrote:

I'm using this formula =IF(ISERROR(AVERAGE(IF(M6:M170,
M6:M17))),"",AVERAGE(IF(M6:M170, 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:M170, M6:M17))),"0",AVERAGE(IF(M6:M170,
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?