I have an array with multiple columns, one of which are start/end times for tasks being completed. I currently use a formula for each line to capture the amount of work hours (factoring in length of work day, holidays, weekends). I then average those results. What I want to do is convert this to a formula in a single cell that will do the same thing, but allow me to do it through a single cell rather than all those lines. I have attached an example spreadsheet to explain this better. This is the current formula.
(NETWORKDAYS(A2,B2,$G$2:$G$11)-1)*(18/24-8/24)+IF(NETWORKDAYS(B2,B2,$G$2:$G$11),MEDIAN(MOD(B2,1),8/24,18/24),18/24)-MEDIAN(NETWORKDAYS(A2,A2,$G$2:$G$11)*MOD(A2,1),8/24,18/24)
Where A2 is the start time/date and B2 is the end time/date. I also need it to factor in other criteria, in the example there are two other columns and I have selected P3 and Marketing. So I would want the average work days, for only a certain priority and a certain team.
Bookmarks