# Capturing work days on average from an array of start/end times, other criteria

1. ## Capturing work days on average from an array of start/end times, other criteria

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.  Register To Reply

2. ## Re: Capturing work days on average from an array of start/end times, other criteria

Can you explain what the numbers in L represent. You seem to be calculating a number that modifies the working days into working hours  Register To Reply

3. ## Re: Capturing work days on average from an array of start/end times, other criteria

The N column (N2:N16) can be calculated by the array formula ``Please Login or Register  to view this content.``
which set the name Start as
Formula:  `Please Login or Register  to view this content.`

and the End name as
Formula:  `Please Login or Register  to view this content.`

\$ N \$ 1 = "Marketing" and \$O\$1="P3" are the filters.
I am not being able to calculate the average of the values of this array (N2:N16).  Register To Reply

4. ## Re: Capturing work days on average from an array of start/end times, other criteria

With a pivot table.

See the attached file.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 