| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Ignore errors when calculation average of multiple ranges | Excel Worksheet Functions | |||
| How do I get an average for 5 when I need to skip cells? | Excel Discussion (Misc queries) | |||
| How do I ignore cells with errors when calculating an average? | Excel Worksheet Functions | |||
| What is this kind of average called? | Excel Worksheet Functions | |||
| Average Column, but Skip Null and 0? | Excel Worksheet Functions | |||
Linear Mode
