Link to home
Start Free TrialLog in
Avatar of Frank Freese
Frank FreeseFlag for United States of America

asked on

Better way to Average to avoid #DIV! showing

Folks,
A few moments ago a solution was correctly present on the correct way to exclude a zero in an average.
With this formula: =AVERAGEIF(A1:A10,"<0>") gives me #DIV! error.
Now let's take this to the next level. Average a range excluding zero without #DIV! or other error code. I tried this:
=IFERROR(AVERGEIF(A1:A10,"<0">)) and it failed.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

I'll look at it but try

=IFERROR(AVERGEIF(A1:A10,"<0>"

A quote mark was misplaced.
Hi,

Try this entered as an array formula:

=AVERAGE(IF(IFERROR(A1:A10,FALSE)<>0,IFERROR(A1:A10,FALSE)))

To enter an array formula, type the formula as above, but when finalising it, use Shift-Ctrl-Enter rather than just Enter, and Excel will *display* it surrounded with curly braces in the formula bar.

Alan.
Avatar of Frank Freese

ASKER

Alan3285 - still get the #DIV/0 error
Martin - typo error on my part. I meant:
=IFERROR(AVERGEIF(A1:A10,"<0>")) . that failed
SOLUTION
Avatar of Flyster
Flyster
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Look good Martin - I was close
Thanks
Allan,
I see the reason I got the error. I had to values in the range. Once I did it worked.
I like both solutions.
great options
thanks to all
Your welcome and just to point out, my solution doesn't need anything in Col A in order to work.
Martin,
Have you tried the solution? If you put in 10,0,10,15,15 what do you get with your formula?
The answer should be 12.50?
The solution being the one you offered
Martin,
I went with Flyster ID: 40210492.
=IFERROR(AVERAGEIF(A1:A10,"<>0"),0)