Excel Gurus brain required :) on averages on number of working days in the month/ week

Hi all

I am in need of some help on an excel query

I am trying to get an average on a weekly basis (for the year) based on number of working days in the month. Im very unsure on how to tackle this. I know i can use the Net working days calc (to break it down for how many working days i need for the month as im excluding bank holidays), However im unsure how to break this down by week as some weeks ill have 4 days.
Please see an extract example below.

01/01/2013 31/01/2013 22(workingdays in month / Excluding the 1st jan)



Example of data which needs breaking down weekly



Year Sum of Jan-13 Sum of Feb-13 Sum of Mar-13 Sum of Apr-13 Sum of May-13 Sum of Jun-13 Sum of Jul-13 Sum of Aug-13 Sum of Sep-13 Sum of Oct-13 Sum of Nov-13 Sum of Dec-13
2 33,000,000 28,600,000 26,812,500 28,600,000 22,533,333 24,142,857 31,571,429 32,000,000 33,000,000 33,000,000 26,000,000 15,000,000
3 16,000,000 13,866,667 13,000,000 13,866,667 13,866,667 14,857,143 14,857,143 12,000,000 12,000,000 14,000,000 14,000,000 11,000,000
5 12,000,000 14,733,333 13,000,000 14,733,333 8,666,667 9,285,714 10,214,286 10,000,000 11,000,000 10,000,000 5,000,000 3,000,000


Any sugguestions on how to tackle this.


kind regards

sorry all the data didnt appear right

Year Sum of Jan-13 Sum of Feb-13 Sum of Mar-13 Sum of Apr-13 Sum of May-13 Sum of Jun-13 Sum of Jul-13 Sum of Aug-13 Sum of Sep-13 Sum of Oct-13 Sum of Nov-13 Sum of Dec-13
2 33,000,000 28,600,000 26,812,500 28,600,000 22,533,333 24,142,857 31,571,429 32,000,000 33,000,000 33,000,000 26,000,000 15,000,000
3 16,000,000 13,866,667 13,000,000 13,866,667 13,866,667 14,857,143 14,857,143 12,000,000 12,000,000 14,000,000 14,000,000 11,000,000
5 12,000,000 14,733,333 13,000,000 14,733,333 8,666,667 9,285,714 10,214,286 10,000,000 11,000,000 10,000,000 5,000,000 3,000,000 [/SUP]

hi Fdibbins,

I have attached an example
sheet 1 shows some data and on the right hand of the sheet it shows (inyellow) the working days and what days to exclude (eg bank holidays)
sheet 2 shows a manuall cal on how id do this (which is just too time consuming )
sheet 3 shows a summary on how id present the data.

thanks for your help

ps im open to suggestions on how to tackle this

many thanks S

hi mr dibbins
this looks good i have one problem in column E how can i exclude data such as bankholidays on the unautomated sheet

Thanks you very much for helping me

Hi Fdbibbins Thanks you very much for the help i really appreciate it x