Forum Discussion
AVERAGEIFS
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.
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.
- Can you post some sample file? It will help us to assist you faster.
- 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.
