SOLVED

AVERAGEIFS

%3CLINGO-SUB%20id%3D%22lingo-sub-2741393%22%20slang%3D%22en-US%22%3ERe%3A%20AVG%20and%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2741393%22%20slang%3D%22en-US%22%3ECan%20you%20post%20some%20sample%20file%3F%20It%20will%20help%20us%20to%20assist%20you%20faster.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2741304%22%20slang%3D%22en-US%22%3EAVG%20and%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2741304%22%20slang%3D%22en-US%22%3E%3CP%3EHopefully%20this%20is%20easy%2C%20but%20I%20just%20can't%20wrap%20my%20head%20around%20how%20long%20this%20formula%20(how%20many%20nested%20formulas)%20is%20going%20to%20be.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20table%20(not%20named%2C%20but%20could%20be%20if%20easier)%20on%20one%20sheet%20(%22Gang%20Time%22)%20with%20column%20D%20blank%20and%20column%20E%20with%20a%20%22sorting%20code%22.%20I%20need%20column%20D%20to%20look%20for%20the%20value%20in%20E%20on%20a%20different%20tab%20(%22Total%20Labor%22)%2C%20column%20E%2C%20also%20(table%20it's%20looking%20in%20is%20A1%3AM5000).%20Then%20it%20needs%20to%20look%20at%20column%20K%20for%20%22Regular%20Worked%20Hours%22%2C%20then%20in%20Column%20L%20and%20only%20return%20the%20value%20if%20it%20is%20greater%20than%203%20and%20less%20than%2013.%20The%20value%20returned%20needs%20to%20be%20added%20up%20for%20all%20matching%20sorting%20codes%20and%20return%20the%20average%20to%202%20decimal%20places.%3C%2FP%3E%3CP%3ESo%2C%20if%20col%20E%20on%20%22Gang%20Time%22%20%3D%205%2C%20col%20D%20should%20average%20all%20%225's%22%20in%20the%20table%20on%20%22Total%20Labor%22%20that%20are%20Regular%20Worked%20Hours%20between%203%20and%2013.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConfusing%20enough%3F%20I'm%20assuming%20it's%20an%20IF(VLOOKUP%2C(IF(VLOOKUP%2C(IFVLOOKUP.......)))))))))%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2741304%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2745660%22%20slang%3D%22en-US%22%3ERe%3A%20AVG%20and%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2745660%22%20slang%3D%22en-US%22%3Eadded.%20Thank%20you%3C%2FLINGO-BODY%3E
Occasional Contributor

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.

3 Replies
Can you post some sample file? It will help us to assist you faster.
added. Thank you
best response confirmed by RandomPanda (Occasional Contributor)
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.