View original (French)

Average without empty cell or null value

Solved
yfchauer Posted messages 84 Status Membre -  
 Hako -
Hello,

Hello everyone,
I'm looking for a formula that will allow me to calculate the average of a range while ignoring empty cells or those with a value of '0'
e.g.
calculate the average of the range (A5:L5) in cell M5 with the formula
=IF(COUNTA(A5:L5)=0,"";AVERAGE(A5:L5)
It works for empty cells.

However, since the data in this range is imported from another sheet, the average is incorrect when some cells in the range are empty
but implied to have a value of "0"
Thank you in advance for your help.

Configuration: win xp home

--
Ambition is like riding a bike. Once you stop pedaling, you fall.

3 réponses

Réponse 1 / 3
tontong Posted messages 2575 Registration date   Status Membre Last intervention   1 064
 
Hello,
Even though it is mathematically incorrect to exclude zeros from an average, here are 3 formulas:
Standard =SUM(A1:A8)/(COUNT(A1:A8)-COUNTIF(A1:A8,"=0"))
Array {=AVERAGE(IF(A1:A8<>0,A1:A8))}
Excel 2007 or later =AVERAGEIF(A1:A8,"<>0")
Be careful with cells that contain formulas and appear empty.
113
yfchauer Posted messages 84 Status Membre 2
 
thank you uncle,
I just tried the 3 formulas
they meet my expectations
perfect

--
Ambition is like riding a bicycle. Once you stop pedaling, you fall.
1
yfchauer Posted messages 84 Status Membre 2
 
Hello,

Thank you for your help, the different formulas are working.
I am delighted.
But I just noticed that it returns a division by 0 on a line where no data is retrieved.
As I mentioned earlier, the data is imported from another sheet.
So, some rows may have no data, and when I increment the formula, I get "#DIV/0".
Now, I would like to know how to avoid this error.
Once again, thank you for your help.
0