[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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.
0
Frank Freese
Asked:
Frank Freese
  • 7
  • 3
  • 2
  • +1
3 Solutions
 
Martin LissRetired ProgrammerCommented:
I'll look at it but try

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

A quote mark was misplaced.
0
 
AlanConsultantCommented:
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.
0
 
Frank FreeseAuthor Commented:
Alan3285 - still get the #DIV/0 error
Martin - typo error on my part. I meant:
=IFERROR(AVERGEIF(A1:A10,"<0>")) . that failed
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
FlysterCommented:
Try this:

=IFERROR(AVERAGEIF(A1:A10,"<>0"),0)

Change the last 0 to "" if you don't want anything to show on error.

Flyster
0
 
Martin LissRetired ProgrammerCommented:
Just do

=IFERROR(AVERAGE(A1:A10),"<0>")
0
 
AlanConsultantCommented:
Hi,

fh_freese - 2014-07-22 at 12:30:47 - ID: 40210489
Alan3285 - still get the #DIV/0 error
Martin - typo error on my part. I meant:
=IFERROR(AVERGEIF(A1:A10,"<0>")) . that failed
Odd - I don't get a #Div/0 error.

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

The data set I have is (A1:A10):

2,4,6,8,10,12,0,0,14,16

If I delete those zeros, and leave those two cells (A7 and A8) blank, I still don't get a Div0 error.

Did you enter the formula as an array?

Alan.
0
 
Frank FreeseAuthor Commented:
Look good Martin - I was close
Thanks
0
 
Frank FreeseAuthor Commented:
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.
0
 
Frank FreeseAuthor Commented:
great options
thanks to all
0
 
Martin LissRetired ProgrammerCommented:
Your welcome and just to point out, my solution doesn't need anything in Col A in order to work.
0
 
Frank FreeseAuthor Commented:
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?
0
 
Frank FreeseAuthor Commented:
The solution being the one you offered
0
 
Frank FreeseAuthor Commented:
Martin,
I went with Flyster ID: 40210492.
=IFERROR(AVERAGEIF(A1:A10,"<>0"),0)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now