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

1. ## 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

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

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]

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

Hi skipton and welcome to the forum

perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

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

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.

ps im open to suggestions on how to tackle this

many thanks S

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

thanks for the file.

Im not there yet, but take a look at the approach I am using and see if it's something you can work with?

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

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

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

I am taking holidays (from DATA sheet, R:Y) into account in column C. If you look at the entries, they match your "manual" entries in column H (i moved your data off to the side so that you could see what you had before)

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

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

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