| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
excluding valuse from an average
I am setting up a tracking spread sheet and I would like to be able to
average the weekly values and then average this for a monthly and a cumulative monthly average. The problem that I am having is there are certain weeks that there won't be any values. Is there a way to exclude these reading ( I am calling them "0" in this spread sheet ) and still be able to use the average function? Thanks in advance for any and all help in this matter |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excluding valuse from an average
If you leave them blank rathr than zero, AVERAGE() will ignore them.
-- Gary's Student "toot033" wrote: I am setting up a tracking spread sheet and I would like to be able to average the weekly values and then average this for a monthly and a cumulative monthly average. The problem that I am having is there are certain weeks that there won't be any values. Is there a way to exclude these reading ( I am calling them "0" in this spread sheet ) and still be able to use the average function? Thanks in advance for any and all help in this matter |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excluding valuse from an average
Enter this an array formula Ctrl+Shift+Enter
=AVERAGE(IF(A1:A100<0,A1:A100)) or enter as normal: =SUM(A1:A100)/(COUNTIF(A1:A100,"0")+COUNTIF(A1:A100,"<0")) HTH "toot033" wrote: I am setting up a tracking spread sheet and I would like to be able to average the weekly values and then average this for a monthly and a cumulative monthly average. The problem that I am having is there are certain weeks that there won't be any values. Is there a way to exclude these reading ( I am calling them "0" in this spread sheet ) and still be able to use the average function? Thanks in advance for any and all help in this matter |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excluding Zero's from Average (SumIF / CountIF) | Excel Worksheet Functions | |||
| average of several cells excluding the minimum | Excel Discussion (Misc queries) | |||
| Is there a way to get an average, excluding the lowest number? | Excel Discussion (Misc queries) | |||
| Average non continguous cells, excluding zero's | Excel Worksheet Functions | |||
| EXcluding Zeros from the average in a row | Excel Discussion (Misc queries) | |||
Linear Mode
