Averages : exclude past month, exclude 0, ignore #div/0

[rshankey]

Example data Below is what I have as my first week of the month for tracking employe status'.
(details)
The cells are using the following formula to pull their data from another sheet.
{=SUM(IF($B$1&G$55=admp!$O$2:$O$10025,admp!$G$2:$G$10025))} , with this I am matching employe name (b1) and dates, to get my data pulled from a data sheet.
For the first week of the month I am looking to exclude the previous months data using the formula below:
=AVERAGEIF($C$55:$G$55,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),C56:G56)
[TABLE="width: 926"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]e[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"]g[/TD]
[TD="align: center"]h[/TD]
[TD="align: center"]i[/TD]
[TD="align: center"]j[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]28-Mar-16[/TD]
[TD="align: right"]29-Mar-16[/TD]
[TD="align: right"]30-Mar-16[/TD]
[TD="align: right"]31-Mar-16[/TD]
[TD="align: right"]1-Apr-16[/TD]
[TD]Baseline[/TD]
[TD]Average[/TD]
[TD]Metric[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]702[/TD]
[TD="align: right"]43[/TD]
[TD="align: center"]55[/TD]
[TD]43[/TD]
[TD]After Call[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]527[/TD]
[TD="align: right"]31[/TD]
[TD="align: center"]30[/TD]
[TD]31[/TD]
[TD]Lunch[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]284[/TD]
[TD="align: right"]15[/TD]
[TD="align: center"]15[/TD]
[TD]15[/TD]
[TD]Break[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]178[/TD]
[TD="align: right"]10[/TD]
[TD="align: center"]15[/TD]
[TD]10[/TD]
[TD]Personal[/TD]
[/TR]
</tbody>[/TABLE]


All of this seems to work without issue. Then I found some days (this last week of the month) where employees were either not present or left early, causing status' to have a 0 time.

At this time I need to be able to average the individual rows while ignoring 0 which I was able to do with :
=AVERAGEIF(C74:G74,"<>0")
This works and ignores zero's, however I have some employee's who have nothing but 0 for status' for this week and using the above formula provides a #div/0! error, which I understand but need to get around.

I had some help to write =IF(ISERROR(AVERAGE(C80:G80)),0,(AVERAGE(C80:G80)))
but this ends up including 0 in the averaging. Is there a better way to average these rows and return a 0 or blank instead of an err if only zero's are present to average

Below is what I have for an employe this week who has missed days and had to leave early, and as a result only has minimal data to average.

[TABLE="width: 926"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]e[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"]g[/TD]
[TD="align: center"]h[/TD]
[TD="align: center"]i[/TD]
[TD="align: center"]j[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]25-apr-16[/TD]
[TD="align: right"]26-apr-16[/TD]
[TD="align: right"]27-apr-16[/TD]
[TD="align: right"]28-apr-16[/TD]
[TD="align: right"]29-apr-16[/TD]
[TD]Baseline[/TD]
[TD]Average[/TD]
[TD]Metric[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]55[/TD]
[TD]4.20[/TD]
[TD]After Call[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]30[/TD]
[TD]0.00[/TD]
[TD]Lunch[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]15[/TD]
[TD]0.00[/TD]
[TD]Break[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD]15[/TD]
[TD]0.00[/TD]
[TD]Personal[/TD]
[/TR]
</tbody>[/TABLE]

using =AVERAGEIF(C80:G80,"<>0") I get the correct result of 21 for the aftercall status. Though because the employee has missed time does not have data for any of the other status and the above formula provides a #div/0 which then provides a #div/0 error when I try to average thats status's weekly averages for the month.
this is where I tried the =IF(ISERROR(AVERAGE(C80:G80)),0,(AVERAGE(C80:G80)))
but the result was 4.2 which is 0+0+21+0+0/5

Any insights would be greatly appreciated. This forum has helped me get this sheet up and running. Hopefully you guys can get me through this last little bit.

Regards,

 

[jorismoerings]

Hi,

With the provided information given i would think the solution isn't in the logic you use to average your data but will be in the logic you use to summarize the data into the provided table's. You almost figured it out by saying "where employees were either not present or left early, causing status' to have a 0 time."

What you tried was to exclude the 0 in your calculation but what you've forgot is: 0 time can be a valid result from the formula if an employee is actually present.

So my guess would be to:

1. Change the formula you're using to pull data to your summary and have it reflect a <blank> blank if there's no data (caused by the employee either not present or left early) instead of 0 and use the 0 just as a result of pulled data (caused by a present employee not doing the task).</blank>
2. Revert the average formula's to the original ones without excluding the 0.

Hope this helps.

 

[XLearner]

Hi,

Try

=IFERROR(AVERAGE(IF(ISERROR($C80:G80), FALSE,
                 IF($C80:G80<>0,IF($C$55:G$55>=DATE(YEAR(TODAY()),MONTH(TODAY()),1),$C80:G80)))),
         "Not enough data")

Validate with [Ctrl]+[Shift]+[Enter] (array formula) and copy down as necessary

Regards
XLearner