+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    01-23-2013
    Location
    england
    MS-Off Ver
    Excel 207
    Posts
    5

    Unhappy 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. #2
    Registered User
    Join Date
    01-23-2013
    Location
    england
    MS-Off Ver
    Excel 207
    Posts
    5

    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. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,311

    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.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    01-23-2013
    Location
    england
    MS-Off Ver
    Excel 207
    Posts
    5

    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.

    thanks for your help

    ps im open to suggestions on how to tackle this

    many thanks S
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,311

    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?
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-23-2013
    Location
    england
    MS-Off Ver
    Excel 207
    Posts
    5

    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. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,311

    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. #8
    Registered User
    Join Date
    01-23-2013
    Location
    england
    MS-Off Ver
    Excel 207
    Posts
    5

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1