average acts different in rows and columns?

[Meenie]

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

 

[Luke M]

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

 

[Bernard Liengme]

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

 

[Meenie]

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%

 

[David Biddulph]

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.

 

[Meenie]

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

 

[Meenie]

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