Average formula only works partially.....

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: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 (because it initially returned a #div/0 error)....I tried
to change the formula to
=IF(ISERROR(AVERAGE(IF(M6:M170,I6:M17))),"0",AVE RAGE(IF(M6:M170,
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....

Try this:

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

--
Biff
Microsoft Excel MVP


"acbel40" wrote in message
...
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: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 (because it initially returned a #div/0 error)....I
tried
to change the formula to
=IF(ISERROR(AVERAGE(IF(M6:M170,I6:M17))),"0",AVE RAGE(IF(M6:M170,
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....

I would do something like this:
=IF(ISERROR(AVERAGE(IF($B$2:$B$1010,$B$2:$B$101) )),"",AVERAGE(IF($B$2:$B$1010,$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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"acbel40" wrote:

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: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 (because it initially returned a #div/0 error)....I tried
to change the formula to
=IF(ISERROR(AVERAGE(IF(M6:M170,I6:M17))),"0",AVE RAGE(IF(M6:M170,
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....

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

If this post helps click Yes
---------------
Jacob Skaria


"acbel40" wrote:

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: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 (because it initially returned a #div/0 error)....I tried
to change the formula to
=IF(ISERROR(AVERAGE(IF(M6:M170,I6:M17))),"0",AVE RAGE(IF(M6:M170,
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....

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" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"acbel40" wrote:

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: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 (because it initially returned a #div/0 error)....I tried
to change the formula to
=IF(ISERROR(AVERAGE(IF(M6:M170,I6:M17))),"0",AVE RAGE(IF(M6:M170,
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....

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
---------------
Jacob Skaria


"acbel40" wrote:

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" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"acbel40" wrote:

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: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 (because it initially returned a #div/0 error)....I tried
to change the formula to
=IF(ISERROR(AVERAGE(IF(M6:M170,I6:M17))),"0",AVE RAGE(IF(M6:M170,
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....

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

"Jacob Skaria" wrote:

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

If this post helps click Yes
---------------
Jacob Skaria


"acbel40" wrote:

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: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 (because it initially returned a #div/0 error)....I tried
to change the formula to
=IF(ISERROR(AVERAGE(IF(M6:M170,I6:M17))),"0",AVE RAGE(IF(M6:M170,
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....