AVERAGEIFS

[RandomPanda]

Hopefully this is easy, but I just can't wrap my head around how long this formula (how many nested formulas) is going to be.

Have a table (not named, but could be if easier) on one sheet ("Gang Time") with column D blank and column E with a "sorting code". I need column D to look for the value in E on a different tab ("Total Labor"), column E, also (table it's looking in is A1:M5000). Then it needs to look at column K for "Regular Worked Hours", then in Column L and only return the value if it is greater than 3 and less than 13. The value returned needs to be added up for all matching sorting codes and return the average to 2 decimal places.

So, if col E on "Gang Time" = 5, col D should average all "5's" in the table on "Total Labor" that are Regular Worked Hours between 3 and 13. 

Confusing enough? I'm assuming it's an IF(VLOOKUP,(IF(VLOOKUP,(IFVLOOKUP.......)))))))))

Thanks in advance.

[#2]

Can you post some sample file? It will help us to assist you faster.

[#3]

added. Thank you

[#4 · Accepted solution]

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.

[#5]

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")))