Apr 13 2022 03:04 PM - edited Apr 13 2022 07:48 PM
More formula help, please.
The problem I am having is when I use a formula, I need it to look up and return a value when the 1st part of the formula changes.
For example, if I want A2 on another sheet to look up OP# 2 in column 2, then look up Date 2 and return Average 2 within that yellow box. Or if I want it to look up OP# 3 then only return the average in the blue box.
When I write out the formula I don't know how to keep it from trying to look up all of Date 2's (in that column) and returning all of the Average 2's (in that column).
I need a formula that will look up only in that colored block based on the OP# I want to look up. I hope that makes sense.
Apr 14 2022 02:07 AM
=CHOOSE(IF($A2=1234,1,IF($A2=5678,2,IF($A2=9012,3,IF($A2=3456,4,IF($A2=7890,5))))),IF(NOT(ISNA(VLOOKUP(F$1,'Audit Sheets'!$B$11:$E$31,4,FALSE))),VLOOKUP(F$1,'Audit Sheets'!$B$11:$E$31,4,FALSE),VLOOKUP(F$1,'Audit Sheets'!$I$11:$L$31,4,FALSE)),,IF(NOT(ISNA(VLOOKUP(F$1,'Audit Sheets'!$B$49:$E$69,4,FALSE))),VLOOKUP(F$1,'Audit Sheets'!$B$49:$E$69,4,FALSE),VLOOKUP(F$1,'Audit Sheets'!$I$49:$L$69,4,FALSE)),,IF(NOT(ISNA(VLOOKUP(F$1,'Audit Sheets'!$B$87:$E$107,4,FALSE))),VLOOKUP(F$1,'Audit Sheets'!$B$87:$E$107,4,FALSE),VLOOKUP(F$1,'Audit Sheets'!$I$87:$L$107,4,FALSE)))
Maybe with this formula which seems to work in the attached file. However there has to be an easier solution.
Apr 14 2022 06:30 PM
Apr 14 2022 07:28 PM
Apr 15 2022 02:00 AM
=IF(NOT(ISNA(VLOOKUP(F$1,OFFSET('Audit Sheets'!$G$3,MATCH('Voice Stats by Start Groups'!$A2,'Audit Sheets'!$G$3:$G$79,0)+7,-5,21,4),4,FALSE))),VLOOKUP(F$1,OFFSET('Audit Sheets'!$G$3,MATCH('Voice Stats by Start Groups'!$A2,'Audit Sheets'!$G$3:$G$79,0)+7,-5,21,4),4,FALSE),VLOOKUP(F$1,OFFSET('Audit Sheets'!$G$3,MATCH('Voice Stats by Start Groups'!$A2,'Audit Sheets'!$G$3:$G$79,0)+7,2,21,4),4,FALSE))
OFFSET is probably the best choice to do this. The OFFSET formula is easier and the other formula uses CHOOSE which is limited to 254 values as far as i know.
Apr 19 2022 11:43 PM
@OliverScheurich I have another part that I need help with. I am not quite good enough to understand your formula to adjust it to what I need. I have what I need help with on the Auditor Sheets tab. Thank you.
Apr 20 2022 02:25 AM
=AVERAGEIFS(AE4:AJ4,AE4:AJ4,">"&0)
*You can try this formula to calculate the average for all cells with values greater than zero. The formula is in cell AK4 of the "Audit Sheets". I had to adjust the layout of the data to apply this formula.
=AVERAGE(OFFSET('Audit Sheets'!$G$3,MATCH(R4,'Audit Sheets'!$G$3:$G$79,0)+12,-2,1,1),OFFSET('Audit Sheets'!$G$3,MATCH(R4,'Audit Sheets'!$G$3:$G$79,0)+20,-2,1,1),OFFSET('Audit Sheets'!$G$3,MATCH(R4,'Audit Sheets'!$G$3:$G$79,0)+28,-2,1,1),OFFSET('Audit Sheets'!$G$3,MATCH(R4,'Audit Sheets'!$G$3:$G$79,0)+12,5,1,1),OFFSET('Audit Sheets'!$G$3,MATCH(R4,'Audit Sheets'!$G$3:$G$79,0)+20,5,1,1),OFFSET('Audit Sheets'!$G$3,MATCH(R4,'Audit Sheets'!$G$3:$G$79,0)+28,5,1,1))
**You can try this formula (which is in cell S4 of the "Audit Sheets") to automatically caluculate the average for every employee. In order to complete the chart you can add the remaining employee numbers in range R7:R1200 and copy the formula across the range S4:S1200.
Apr 20 2022 10:44 PM
Apr 21 2022 02:08 AM
=AVERAGEIFS(AE4:AJ4,AE4:AJ4,">"&0)
I've entered an error in cell E24 and the above formula in cell S4. You can as well use the chart in range AD4:AK6 which has an overview of op numbers, months and averages.