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?
--
Thank you for your time!
John

=IF(ISERROR(AVERAGEIF(A1:A5,"2")),0,AVERAGEIF(A1: A5,"2"))
--
Gary''s Student - gsnu201001


"Very Basic User" wrote:

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?
--
Thank you for your time!
John

--
Thank you for your time!
John


"Gary''s Student" wrote:

=IF(ISERROR(AVERAGEIF(A1:A5,"2")),0,AVERAGEIF(A1: A5,"2"))
--
Gary''s Student - gsnu201001


"Very Basic User" wrote:

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?
--
Thank you for your time!
John

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

--
Thank you for your time!
John


"Gary''s Student" wrote:

=IF(ISERROR(AVERAGEIF(A1:A5,"2")),0,AVERAGEIF(A1: A5,"2"))
--
Gary''s Student - gsnu201001


"Very Basic User" wrote:

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?
--
Thank you for your time!
John

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((A12)*A1,(A52)*A5,(A62)*A6)/INDEX(FREQUENCY((A1,A5:A6),2),2)

With an error trap:

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

--
Biff
Microsoft Excel MVP


"Very Basic User" wrote in message
...
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?
--
Thank you for your time!
John

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!
--
Thank you for your time!
John


"T. Valko" wrote:

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((A12)*A1,(A52)*A5,(A62)*A6)/INDEX(FREQUENCY((A1,A5:A6),2),2)

With an error trap:

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

--
Biff
Microsoft Excel MVP


"Very Basic User" wrote in message
...
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?
--
Thank you for your time!
John


.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Very Basic User" wrote in message
...
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!
--
Thank you for your time!
John


"T. Valko" wrote:

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((A12)*A1,(A52)*A5,(A62)*A6)/INDEX(FREQUENCY((A1,A5:A6),2),2)

With an error trap:

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

--
Biff
Microsoft Excel MVP


"Very Basic User" wrote in
message
...
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?
--
Thank you for your time!
John


.