Forum Discussion
Multiple Criteria
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.
=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.
- I am still having an issue if in one of the cells that you are averaging, it has #DIV/0! in it. So if you put #DIV/0! in cell E24, you will see what I mean.
=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.