Sum Values if weekday

[Nitrousoxide]

I'm trying to create a function that sums the amount of time people worked on a project every week day (but not weekends).

Basically I have a set of data like this:


<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

Number of days since start of project	1.00	2.00	3.00	4.00	5.00	Weekend	Weekend	6.00
Date	2/12/18	2/13/18	2/14/18	2/15/18	2/16/18	2/17/18	2/18/18	2/19/18
Hours
Billcode 1	0	0	0	0	0	0	0	0
Billcode 2	50	60	30	25	22	28	10	25
Billcode 3	1	3	4	6	2	0	0	2
Billcode 4	1	1	3	5	4	1	0	2
Billcode 5	0	2	1	3	8	0	0	7
Billcode 6	0	0	0	0	0	0	0	0

<colgroup><col style="width: 236px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

I want to add up the values for say bill codes 3-6 and divide by the number of weekdays the project has been going on and have a running average number of hours billed each weekday those sets of bill codes combined.

At the start of each day I'll be dropping in the amount of hours billed for the previous day in the right most column, so ideally the function would update every day to give a new average, unless the new day that was added was a weekend day.

Any ideas on how to do this?

I've come up with a way to do this for a 5 day average like this:

=sum(index(1:12,12, match((NETWORKDAYS(B2,today()-1)-0),1:1,0)),index(1:12,12, match((NETWORKDAYS(B2,today()-1)-1),1:1,0)),index(1:12,12, match((NETWORKDAYS(B2,today()-1)-2),1:1,0)),index(1:12,12, match((NETWORKDAYS(B2,today()-1)-3),1:1,0)),index(1:12,12, match((NETWORKDAYS(B2,today()-1)-4),1:1,0)))/5


But obviously something like that doesn't work for a dataset that will constantly be growing.