Forum Discussion
AVERAGEIFS
Figured it out in case anyone else needs to know. I used "AVERAGEIFS" with several criteria and added rounding at the beginning to make my pivot table look better without having 47 decimal places.
EXAMPLE:
=IFERROR(ROUND(AVERAGEIFS('Total Labor'!L:L,'Total Labor'!L:L,"<13",'Total Labor'!L:L,">3",'Total Labor'!K:K,"Regular Worked Hours",'Total Labor'!E:E,E10),2),0) --added error handling so if a department didn't work on that day (Saturdays, mostly) my Pivot Tables wouldn't show #DIV/0
Where I am averaging all Regular Worked Hours between 3 and 13 (removing outliers) for each department number. Table of information is on sheet "Total Labor", Hours to average are in column L. Column K contains Pay Code (i.e. Overtime, Regular Worked Hours, Vacation, etc.) and the department number is in column E.
- added. Thank you
Figured it out in case anyone else needs to know. I used "AVERAGEIFS" with several criteria and added rounding at the beginning to make my pivot table look better without having 47 decimal places.
EXAMPLE:
=IFERROR(ROUND(AVERAGEIFS('Total Labor'!L:L,'Total Labor'!L:L,"<13",'Total Labor'!L:L,">3",'Total Labor'!K:K,"Regular Worked Hours",'Total Labor'!E:E,E10),2),0) --added error handling so if a department didn't work on that day (Saturdays, mostly) my Pivot Tables wouldn't show #DIV/0
Where I am averaging all Regular Worked Hours between 3 and 13 (removing outliers) for each department number. Table of information is on sheet "Total Labor", Hours to average are in column L. Column K contains Pay Code (i.e. Overtime, Regular Worked Hours, Vacation, etc.) and the department number is in column E.Ok, this worked in my original file. But now, corporate has made a bunch of changes to how my payroll file downloads and I needed to create a new file. There are a lot more criteria now.
Here's the setup:
3 worksheets: 1. raw data, 2. formula(s) 3. Listing
Raw data (wksht 1) contains:
Date (col A) / Location (col B) / Work Center (col G) / Pay Code (col L) / Hours (col M) - plus a bunch of other columns, but these are the necessary ones
Listing (wksht 3) contains:
Work Center (col A) / Cost Center Description (col D) -
Formula contains:
<formulas start in row 7>
VLookup to Work Center number ("Listing col A") - in Column A
VLookup to Work Center description ("Listing col B") - in Column B
nested IF/VLookup/IFERROR/AverageIfs - in column C (to be copied to columns D-G. These are days of the week)
Days of the Week (Date) is in row 7 of column C-G
Formula is trying to do several things:
a. Based on "Listing / Cost Center Description" use a particular cell on current worksheet
b. If "Listing / Cost Center Description" matches a particular description AND matches a particular Work Center number - then I need to get the Average of "regular worked hours, between 3 and 13 for the date (in row 7)" from the Raw Data worksheet. This is incorporating the "averageifs" formula that works like a charm from my previous post.
Here is the formula I used - so far I haven't incorporated the part about matching the date because I was just trying to get the average to work. And I don't have all the options in there yet, so "N A F" is "not accounted for" - which means this is getting a LOT longer. But, one step at a time.
Sample file attached
=IF((VLOOKUP($A7,'WORK CENTERS'!$A$2:$D$72,4)="DES MOINES-ANIMAL FOOD"),0,IF(VLOOKUP($A7,'WORK CENTERS'!$A$2:$D$72,4)="DES MOINES-HARVEST",IF(VLOOKUP($A7,'WORK CENTERS'!$A$2:$D$72,1)="100009",IFERROR(ROUND(AVERAGEIFS('Stamp Download'!$M:$M,'Stamp Download'!$M:$M,"<13",'Stamp Download'!$M:$M,">3",'Stamp Download'!$L:$L,"Regular Worked Hours",'Stamp Download'!$E:$E,$A7),2),0),C$1),IF((VLOOKUP($A7,'WORK CENTERS'!$A$2:$D$72,4)="DES MOINES-CUT"),C$2,"N A F")))