average acts different in rows and columns?
 

Excel 2003:
When I do an average of a range of cells the 0 is ignored if the numbers are
in a column and is counted in the average if done across rows. Why is this?
The numbers are percentages between 0 and 100.
If I have the numbers listed down a column :
100.00%
100.00%
0.00%
85.00%
93.33%
100.00%
The average comes out to 95.67%

If I list them across a row:
100.00% 100.00% 0.00% 85.00% 93.33% 100.00%
the average comes out to 79.72%.

thanks Meenie

average acts different in rows and columns?
 

I am unable to duplicate your problem, I get an average of 79.72% regardless
of direction. In your column example, is there really a value of 0 entered
into the excel, or are you forcing a 0 to be displayed? (blank cells in pivot
tables, some data tables, etc.)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Meenie" wrote:

Excel 2003:
When I do an average of a range of cells the 0 is ignored if the numbers are
in a column and is counted in the average if done across rows. Why is this?
The numbers are percentages between 0 and 100.
If I have the numbers listed down a column :
100.00%
100.00%
0.00%
85.00%
93.33%
100.00%
The average comes out to 95.67%

If I list them across a row:
100.00% 100.00% 0.00% 85.00% 93.33% 100.00%
the average comes out to 79.72%.

thanks Meenie

average acts different in rows and columns?
 

To average excluding zeros use
=AVERAGE(IF(A1:A6<0,A1:A6))
This is an array formula so commit it with SHIFT + CTRL + ENTER not just
ENTER
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Meenie" wrote in message
...
Excel 2003:
When I do an average of a range of cells the 0 is ignored if the numbers
are
in a column and is counted in the average if done across rows. Why is
this?
The numbers are percentages between 0 and 100.
If I have the numbers listed down a column :
100.00%
100.00%
0.00%
85.00%
93.33%
100.00%
The average comes out to 95.67%

If I list them across a row:
100.00% 100.00% 0.00% 85.00% 93.33% 100.00%
the average comes out to 79.72%.

thanks Meenie

average acts different in rows and columns?
 

Thre is really a value of 0.00% in the cell:
Month Compliance
Jan 100.00%
Feb 100.00%
*Mar 0.00%
Apr 90.00%
May 93.33%
Jun 100.00%
Jul
Aug
Sep
Oct
Nov
Dec
"YTD
AVERAGE:" 96.67%


"Luke M" wrote:

I am unable to duplicate your problem, I get an average of 79.72% regardless
of direction. In your column example, is there really a value of 0 entered
into the excel, or are you forcing a 0 to be displayed? (blank cells in pivot
tables, some data tables, etc.)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Meenie" wrote:

Excel 2003:
When I do an average of a range of cells the 0 is ignored if the numbers are
in a column and is counted in the average if done across rows. Why is this?
The numbers are percentages between 0 and 100.
If I have the numbers listed down a column :
100.00%
100.00%
0.00%
85.00%
93.33%
100.00%
The average comes out to 95.67%

If I list them across a row:
100.00% 100.00% 0.00% 85.00% 93.33% 100.00%
the average comes out to 79.72%.

thanks Meenie

average acts different in rows and columns?
 

I suggest that you check again with =ISNUMBER(cellref) and =ISTEXT(cellref)
to see what you've really got in the cell that displays as zero.
--
David Biddulph

"Meenie" wrote in message
...
Thre is really a value of 0.00% in the cell:
Month Compliance
Jan 100.00%
Feb 100.00%
*Mar 0.00%
Apr 90.00%
May 93.33%
Jun 100.00%
Jul
Aug
Sep
Oct
Nov
Dec
"YTD
AVERAGE:" 96.67%


"Luke M" wrote:

I am unable to duplicate your problem, I get an average of 79.72%
regardless
of direction. In your column example, is there really a value of 0
entered
into the excel, or are you forcing a 0 to be displayed? (blank cells in
pivot
tables, some data tables, etc.)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Meenie" wrote:

Excel 2003:
When I do an average of a range of cells the 0 is ignored if the
numbers are
in a column and is counted in the average if done across rows. Why is
this?
The numbers are percentages between 0 and 100.
If I have the numbers listed down a column :
100.00%
100.00%
0.00%
85.00%
93.33%
100.00%
The average comes out to 95.67%

If I list them across a row:
100.00% 100.00% 0.00% 85.00% 93.33% 100.00%
the average comes out to 79.72%.

thanks Meenie

average acts different in rows and columns?
 

You are absolutely right-on, David!
The worksheet is a YTD sheet so is linked to another sheet. In the other
sheet, one unit had had an upheaval so had not turned in any audits for that
month so all the cells were blank except the cell for the averages which
showed as 0.00%
That 0.00% was taken to the YTD sheet as 0.00% but when I did =ISNUMBER on
that cell it came back false.
When I went to the linked sheet and put zeros in the cells instead of
leaving them blank, then the YTD answered "true" to =ISNUMBER and the average
calculated correctly!
I would never have connected that! Thanks.
Meenie

"David Biddulph" wrote:

I suggest that you check again with =ISNUMBER(cellref) and =ISTEXT(cellref)
to see what you've really got in the cell that displays as zero.
--
David Biddulph

"Meenie" wrote in message
...
Thre is really a value of 0.00% in the cell:
Month Compliance
Jan 100.00%
Feb 100.00%
*Mar 0.00%
Apr 90.00%
May 93.33%
Jun 100.00%
Jul
Aug
Sep
Oct
Nov
Dec
"YTD
AVERAGE:" 96.67%


"Luke M" wrote:

I am unable to duplicate your problem, I get an average of 79.72%
regardless
of direction. In your column example, is there really a value of 0
entered
into the excel, or are you forcing a 0 to be displayed? (blank cells in
pivot
tables, some data tables, etc.)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Meenie" wrote:

Excel 2003:
When I do an average of a range of cells the 0 is ignored if the
numbers are
in a column and is counted in the average if done across rows. Why is
this?
The numbers are percentages between 0 and 100.
If I have the numbers listed down a column :
100.00%
100.00%
0.00%
85.00%
93.33%
100.00%
The average comes out to 95.67%

If I list them across a row:
100.00% 100.00% 0.00% 85.00% 93.33% 100.00%
the average comes out to 79.72%.

thanks Meenie

average acts different in rows and columns?
 

Thanks Bernard. My problem with this spreadsheet is that I do want the zero
to count .
However, I can see where this formula would be valuable to know and am
saving it :)
Meenie

"Bernard Liengme" wrote:

To average excluding zeros use
=AVERAGE(IF(A1:A6<0,A1:A6))
This is an array formula so commit it with SHIFT + CTRL + ENTER not just
ENTER
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Meenie" wrote in message
...
Excel 2003:
When I do an average of a range of cells the 0 is ignored if the numbers
are
in a column and is counted in the average if done across rows. Why is
this?
The numbers are percentages between 0 and 100.
If I have the numbers listed down a column :
100.00%
100.00%
0.00%
85.00%
93.33%
100.00%
The average comes out to 95.67%

If I list them across a row:
100.00% 100.00% 0.00% 85.00% 93.33% 100.00%
the average comes out to 79.72%.

thanks Meenie