Average excluding zeros

Question

Hello,

I want to calculate an average of certain numbers without considering the 0s, but if there are only 0s, I want my average to be 0.

I have a formula =AVERAGEIFS(range1;criteria1;criteria_range0;"<>0")
Everything works perfectly so far regarding the exclusion of 0s in my average.

However, if I only have 0s in this range, the result is $DIV/0!

I would like that if there are only 0s in my range, the average is 0.

Need help!

Gyrus · Answer

Hello,  You can use the IFERROR function =IFERROR(AVERAGEIF(average_range,criteria_range1,criteria1,criteria_range2,criteria2),0) See you!

Seb · Answer

It's true in the worst case, but I can't foresee any potential future errors... I would have preferred it to give me an average of 0 if there are only 0s...   Thank you anyway.

Fanelga · Answer

Hello,

This formula calculates the average of cells without considering 0:
=AVERAGE(IF(A2:A10>0,A2:A10))

Here’s a small example:
1, 45, 5, 0, 3, 0.
Average with the 0s: (1+45+5+3) / 6 = 9
Average without the 0s (using the formula above): (1+45+5+3) / 4 = 13.5

Note: For this formula to work, you need to validate it by pressing CTRL + ALT + Enter. It seems you have an error otherwise.

Seb · Answer

Sure, but I can't set my criteria, hence my average formula.

Mike-31 · Answer

Hello thread,

I don't see where the problem is that has already been explained by the speakers whom I greet

example for the range A2:A20
=IF(SUM(A2:A20)=0,0,AVERAGEIFS(A2:A20,A2:A20,">0"))

--
Best regards
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

Seb · Answer

Here is an example file to better understand the issue:  http://cjoint.com/?DIfnAeAoNAV

Mike-31 · Answer

Re,

well, the solution has been given to you by Gyrus, in L10 and you increment downwards

=IFERROR(AVERAGEIFS(B10:J10,$B$9:$J$9,"GRAND",B10:J10,"<>0"),0)

to be adapted for the right columns
A+
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

Gyrus · Answer

The proof by example :) https://www.cjoint.com/?DIfonxJwbH0  See you!

seb · Answer

I agree, but in itself the result is ONLY if there is an error and it is not the average result... it's distorted.

Mike-31 · Answer

Re,

As for me, I no longer understand "IF there is an error and it is not the average result... it is distorted" WHAT DO YOU MEAN

this formula that I gave you above handles errors, or explain!
=IFERROR(AVERAGEIFS(B10:J10;$B$9:$J$9;"LARGE";B10:J10;"<>0");0)

--
A+
Mike-31

A period of failure is a perfect time to plant the seeds of knowledge.

seb · Answer

In fact, the 0 comes from the error; it is not the result of an average. That is to say, if it is an error, it sets it to 0.

Mike-31 · Answer

Re,

For my part, I can’t follow you. For example, in cell L12 you have this formula

=AVERAGEIFS(B12:J12,$B$9:$J$9,"GRAND",B12:J12,"<>0")
which generates the error #DIV/0!

If you use this formula
=IFERROR(AVERAGEIFS(B12:J12,$B$9:$J$9,"GRAND",B12:J12,"<>0"),0)

it indeed generates a 0

--
See you
Mike-31

A period of failure is a perfect time to sow the seeds of knowledge.

seb · Answer

YES, once again, but the 0 is the result of the error and not the average of (0+0+0)/3.

In short, if I hadn’t made a mistake, I wouldn’t have a 0. I would like the result to be resolved mathematically and not because there is an error in the formula.

Mike-31 · Answer

Hi,

But it's the same thing now, but more complicated for cell L10 and increment downwards

=IF(SUMIFS(B10:J10,$B$9:$J$9,"GRAND",B10:J10,"<>0")=0,"y",AVERAGEIFS(B10:J10,$B$9:$J$9,"GRAND",B10:J10,"<>0"))

--
Best,
Mike-31

A period of failure is a perfect time to plant the seeds of knowledge.

seb · Answer

Perfect!
But it's strange because the cell understands that if the average of the numbers = 0, while my initial formula was going in the same direction but returned an error.

=IF(SUMIFS(B10:J10,$B$9:$J$9,"GRAND",B10:J10,"<>0")=0,"y",AVERAGEIFS(B10:J10,$B$9:$J$9,"GRAND",B10:J10,"<>0"))

It should have displayed 0 instead of #DIV/0! from the start since it recognizes that SUMIFS(B10:J10,$B$9:$J$9,"GRAND",B10:J10,"<>0")=0

Anyway, thank you and have a great weekend!

seb · Answer

Ah no, it refers to a sum! My bad =)

Gyrus · Answer

I would like you to tell me whether or not you have looked at my file. If you don't want to respond, just tell me m...., it will still be nicer than ignoring me.  See you later!

Average excluding zeros

17 réponses

Texture issues in enshrouded

Announcement: online source code archive

Samsung tv music on startup

Écrire o barré avec un clavier azerty

Application extension issue on ps4

Orange unlimited numbers

Text messages from 36111

Tomb raider anniversary on wii

Notice shows wr30 m

Search for a song in the series foudre