Average ignore #DIV/0! error

good day i should know this formula but for some reason i can't remember. need to average some cells but ignore the dreaded #div error

ex

A25 is 25
A50 is 30
A75 is #div/0!
A100 is 30

so i need to get the average of those but ignore the #div/0!

those cells are a result of an average of a range (a1:a25 etc) so the formula could also be in that range as long as the formula the get the overall average doesn't count in any 0's.

It's not normally a good idea to average averages, you probably won't get the same result as you would if you averaged the original data (because you're not necessarily giving each individual value equal weight).

The solution, therefore is to average the original data range, e.g.

=AVERAGE(A1:A100)

If this might give an error change to

=IF(COUNT(A1:A100),AVERAGE(A1:A100),"")

Originally Posted by daddylonglegs

It's not normally a good idea to average averages, you probably won't get the same result as you would if you averaged the original data (because you're not necessarily giving each individual value equal weight).

The solution, therefore is to average the original data range, e.g.

=AVERAGE(A1:A100)

If this might give an error change to

=IF(COUNT(A1:A100),AVERAGE(A1:A100),"")

you know that saying "all things being equal the simplest answer is usually the correct one" well this is one of those times. good ole simple average did the trick. thx!

I do it with the IF function like this:

=IF(SUM(A1:A100)=0,0,AVERAGE(A1:A100))

Originally Posted by dom_econ

Try

=AVERAGEIF(A1:A100,"<>#DIV/0!")

It worked thanks!