skip errors in an average
 

I'm trying to get an average of a series of averages. Some of the cells in
the series have a 0/div error, which is ok for the series. I can't get the
average of the averages formula to ignore the error cells. Here's what I'm
trying:
=AVERAGEif(M3:M58,"<0").
Can anyone point out the simple solution that I'm overlooking?
Thanks!
Tammie

skip errors in an average
 

This will work commited as an array formula (CTRL+SHIFT+ENTER)

=AVERAGE(IF(ISERROR(M3:M58),"",M3:M58))

--
Regards,
Dave


"tamiluchi" wrote:

I'm trying to get an average of a series of averages. Some of the cells in
the series have a 0/div error, which is ok for the series. I can't get the
average of the averages formula to ignore the error cells. Here's what I'm
trying:
=AVERAGEif(M3:M58,"<0").
Can anyone point out the simple solution that I'm overlooking?
Thanks!
Tammie

skip errors in an average
 

That works as far as making it skip the errors. Thanks.
Can I also make it skip blank cells instead of counting them in the average?

"David Billigmeier" wrote:

This will work commited as an array formula (CTRL+SHIFT+ENTER)

=AVERAGE(IF(ISERROR(M3:M58),"",M3:M58))

--
Regards,
Dave


"tamiluchi" wrote:

I'm trying to get an average of a series of averages. Some of the cells in
the series have a 0/div error, which is ok for the series. I can't get the
average of the averages formula to ignore the error cells. Here's what I'm
trying:
=AVERAGEif(M3:M58,"<0").
Can anyone point out the simple solution that I'm overlooking?
Thanks!
Tammie

skip errors in an average
 

The AVERAGE() function autumatically skips blank cells, no extra coding is
needed.

--
Regards,
Dave


"tamiluchi" wrote:

That works as far as making it skip the errors. Thanks.
Can I also make it skip blank cells instead of counting them in the average?

"David Billigmeier" wrote:

This will work commited as an array formula (CTRL+SHIFT+ENTER)

=AVERAGE(IF(ISERROR(M3:M58),"",M3:M58))

--
Regards,
Dave


"tamiluchi" wrote:

I'm trying to get an average of a series of averages. Some of the cells in
the series have a 0/div error, which is ok for the series. I can't get the
average of the averages formula to ignore the error cells. Here's what I'm
trying:
=AVERAGEif(M3:M58,"<0").
Can anyone point out the simple solution that I'm overlooking?
Thanks!
Tammie

skip errors in an average
 

=AVERAGE(IF(NOT(ISERROR(M3:M58)),IF(M3:M58<"",M3: M58)))

still an array formula.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"tamiluchi" wrote in message
...
That works as far as making it skip the errors. Thanks.
Can I also make it skip blank cells instead of counting them in the
average?

"David Billigmeier" wrote:

This will work commited as an array formula (CTRL+SHIFT+ENTER)

=AVERAGE(IF(ISERROR(M3:M58),"",M3:M58))

--
Regards,
Dave


"tamiluchi" wrote:

I'm trying to get an average of a series of averages. Some of the
cells in
the series have a 0/div error, which is ok for the series. I can't get
the
average of the averages formula to ignore the error cells. Here's what
I'm
trying:
=AVERAGEif(M3:M58,"<0").
Can anyone point out the simple solution that I'm overlooking?
Thanks!
Tammie

skip errors in an average
 

Actually I spoke too soon, and learned something in the process! I guess
when a formula is array entered it *doesn't* ignore blank cells. Change to
this to fix:

=AVERAGE(IF(ISERROR(A1:A15)+ISBLANK(A1:A15),"",A1: A15))

Sorry!
--
Regards,
Dave


"David Billigmeier" wrote:

The AVERAGE() function autumatically skips blank cells, no extra coding is
needed.

--
Regards,
Dave


"tamiluchi" wrote:

That works as far as making it skip the errors. Thanks.
Can I also make it skip blank cells instead of counting them in the average?

"David Billigmeier" wrote:

This will work commited as an array formula (CTRL+SHIFT+ENTER)

=AVERAGE(IF(ISERROR(M3:M58),"",M3:M58))

--
Regards,
Dave


"tamiluchi" wrote:

I'm trying to get an average of a series of averages. Some of the cells in
the series have a 0/div error, which is ok for the series. I can't get the
average of the averages formula to ignore the error cells. Here's what I'm
trying:
=AVERAGEif(M3:M58,"<0").
Can anyone point out the simple solution that I'm overlooking?
Thanks!
Tammie

skip errors in an average
 

I thought it should, but for some reason it's counting my blank cells.
I manipulated it to show each average in my series as 100% so that I could
figure out what the problem was. I have 2 error cells and 8 100% cells. I'm
getting 15% as my overall average so it has to be counting the blank cells.
Right?

"David Billigmeier" wrote:

The AVERAGE() function autumatically skips blank cells, no extra coding is
needed.

--
Regards,
Dave


"tamiluchi" wrote:

That works as far as making it skip the errors. Thanks.
Can I also make it skip blank cells instead of counting them in the average?

"David Billigmeier" wrote:

This will work commited as an array formula (CTRL+SHIFT+ENTER)

=AVERAGE(IF(ISERROR(M3:M58),"",M3:M58))

--
Regards,
Dave


"tamiluchi" wrote:

I'm trying to get an average of a series of averages. Some of the cells in
the series have a 0/div error, which is ok for the series. I can't get the
average of the averages formula to ignore the error cells. Here's what I'm
trying:
=AVERAGEif(M3:M58,"<0").
Can anyone point out the simple solution that I'm overlooking?
Thanks!
Tammie

skip errors in an average
 

That does it. Thanks so much.
:-)
Tammie

"David Billigmeier" wrote:

Actually I spoke too soon, and learned something in the process! I guess
when a formula is array entered it *doesn't* ignore blank cells. Change to
this to fix:

=AVERAGE(IF(ISERROR(A1:A15)+ISBLANK(A1:A15),"",A1: A15))

Sorry!
--
Regards,
Dave


"David Billigmeier" wrote:

The AVERAGE() function autumatically skips blank cells, no extra coding is
needed.

--
Regards,
Dave


"tamiluchi" wrote:

That works as far as making it skip the errors. Thanks.
Can I also make it skip blank cells instead of counting them in the average?

"David Billigmeier" wrote:

This will work commited as an array formula (CTRL+SHIFT+ENTER)

=AVERAGE(IF(ISERROR(M3:M58),"",M3:M58))

--
Regards,
Dave


"tamiluchi" wrote:

I'm trying to get an average of a series of averages. Some of the cells in
the series have a 0/div error, which is ok for the series. I can't get the
average of the averages formula to ignore the error cells. Here's what I'm
trying:
=AVERAGEif(M3:M58,"<0").
Can anyone point out the simple solution that I'm overlooking?
Thanks!
Tammie

skip errors in an average
 

no problem, happy to help.
Thanks for the feedback.

--
Regards,
Dave


"tamiluchi" wrote:

That does it. Thanks so much.
:-)
Tammie

"David Billigmeier" wrote:

Actually I spoke too soon, and learned something in the process! I guess
when a formula is array entered it *doesn't* ignore blank cells. Change to
this to fix:

=AVERAGE(IF(ISERROR(A1:A15)+ISBLANK(A1:A15),"",A1: A15))

Sorry!
--
Regards,
Dave


"David Billigmeier" wrote:

The AVERAGE() function autumatically skips blank cells, no extra coding is
needed.

--
Regards,
Dave


"tamiluchi" wrote:

That works as far as making it skip the errors. Thanks.
Can I also make it skip blank cells instead of counting them in the average?

"David Billigmeier" wrote:

This will work commited as an array formula (CTRL+SHIFT+ENTER)

=AVERAGE(IF(ISERROR(M3:M58),"",M3:M58))

--
Regards,
Dave


"tamiluchi" wrote:

I'm trying to get an average of a series of averages. Some of the cells in
the series have a 0/div error, which is ok for the series. I can't get the
average of the averages formula to ignore the error cells. Here's what I'm
trying:
=AVERAGEif(M3:M58,"<0").
Can anyone point out the simple solution that I'm overlooking?
Thanks!
Tammie