AverageIF

[Very Basic User]

I have two questions.

1. When using AVERAGEIF... actual line =AVERAGEIF(A1:A5,">2") this works
great unless all values = 0 then I get #DIV/0!. I would like to have the
value remain 0 (The greater than 2 is because we have a +;- 2 standard error
in our insequel pull of information. How would I correct the error message?

2. When using AVERAGEIF for cells not in a range...actual line
=AVERAGEIF(A1,A5,A6,">2") this formula is not acceptable at all. Any way to
do this?

 

[Gary''s Student]

=IF(ISERROR(AVERAGEIF(A1:A5,">2")),0,AVERAGEIF(A1:A5,">2"))

 

[T. Valko]

Try these...

1:

=IFERROR(AVERAGEIF(A1:A5,">2"),0)

2: no elegant way to do this one unless the non-contiguous cells follow a
set pattern (every other cell, every 5th cell, every 10th cell, etc.)

=SUM((A1>2)*A1,(A5>2)*A5,(A6>2)*A6)/INDEX(FREQUENCY((A1,A5:A6),2),2)

With an error trap:

=IFERROR(SUM((A1>2)*A1,(A5>2)*A5,(A6>2)*A6)/INDEX(FREQUENCY((A1,A5:A6),2),2),0)

 

[Very Basic User]

--
Thank you for your time!
John

=IF(ISERROR(AVERAGEIF(A1:A5,">2")),0,AVERAGEIF(A1:A5,">2"))

 

[Very Basic User]

Gary's student your solution was perfect for my first question. Thank you!

 

[Very Basic User]

T. Valko, thank you very much I was afraid of that. I actually started by
just transfering summed cells to another location, having the code read to
those cells and then hiding the columns. This is a better way to keep it
clean! thank you!

 

[T. Valko]

You're welcome. Thanks for the feedback!