Excel 2007 counting empty cells in Average, Count and other functions

[Michael Kensy]

Hola Forum

I recently came across a file where the AVERAGE function calculates its value base on the number of all cells no matter if they contain a value or not. Please view the example below:

Excel Workbook
	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU
2	80	80			80	75	90	60	65	50	60	65	40	60	60	60		65	60	75	85	85		65	65	80	75	80	83	69.2	24
Hospitation Record

Even using the COUNTIF function with a condition >0 does not use the expected divisior. At the same time the COUNTBLANKS does recognize these same cells. I further on used the GoTo, Special, Blanks and Cleared, All command but still those empty cells are counted.

Anyone any idea why?

The original file is generated on a CHINESE SETUP PC, my one is a US SETUP. Both PCs got Office 2007 installed.
--
Cheers Michael

 

[Aladin Akyurek]

[1] =SUM(AQ2:BR2)/(COUNT(AQ2:BR2)-COUNTBLANK(AQ2:BR2))

is a wrong calculation, that is, it's not an average what it calculates.

Both:

[2] =AVERAGE(AQ2:BR2)

[3] =SUM(AQ2:BR2)/COUNT(AQ2:BR2)

ignore empty/blank cells, while [1] does not not because COUNTBLANK counts both empty (unused) and blanks (e.g., created by ="").

To recap: the denominator the formula in [1] uses is wrong for an ordinary average.

 

[Michael Kensy]

Dont know what was wrong with my brain yesterday, I obviously got a used day.


Sorry for annoying
--
Michael