Frank Freese
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.
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,"
Hi,
Try this entered as an array formula:
=AVERAGE(IF(IFERROR(A1:A10 ,FALSE)<>0 ,IFERROR(A 1:A10,FALS E)))
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.
Try this entered as an array formula:
=AVERAGE(IF(IFERROR(A1:A10
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.
ASKER
Alan3285 - still get the #DIV/0 error
Martin - typo error on my part. I meant:
=IFERROR(AVERGEIF(A1:A10," <0>")) . that failed
Martin - typo error on my part. I meant:
=IFERROR(AVERGEIF(A1:A10,"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Look good Martin - I was close
Thanks
Thanks
ASKER
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.
I see the reason I got the error. I had to values in the range. Once I did it worked.
I like both solutions.
ASKER
great options
thanks to all
thanks to all
Your welcome and just to point out, my solution doesn't need anything in Col A in order to work.
ASKER
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?
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?
ASKER
The solution being the one you offered
ASKER
Martin,
I went with Flyster ID: 40210492.
=IFERROR(AVERAGEIF(A1:A10, "<>0"),0)
I went with Flyster ID: 40210492.
=IFERROR(AVERAGEIF(A1:A10,
=IFERROR(AVERGEIF(A1:A10,"
A quote mark was misplaced.