+ Reply to Thread
Results 1 to 10 of 10

average weekday ongoing last 3 months

  1. #1
    Registered User
    Join Date
    03-05-2020
    Location
    Ottawa, Ontario
    MS-Off Ver
    16.34
    Posts
    5

    average weekday ongoing last 3 months

    I am using a Mac
    I have a work book, 2 sheets - 1st sheet contains columns with dates (full year) , rows are 1/2 time slots and in those time slots are whole numbers
    I am trying to do a formula that will calculate and ongoing average for the last 3 months (workdays only) to put onto sheet 2

    What I have done (and was going to just manually change every month) is:

    =AVERAGE(IF(WEEKDAY('Sheet1'!$B$2:'Sheet1'!$NC$2,2)<6,'Sheet1'!$B4:'Sheet1'!$NC4))

    But the average number is not coming up correctly it's reading 0.5 instead of 3.1 (as it should)

    I have no idea what I'm doing wrong and I can't find an answer on google that is what I need

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: average weekday ongoing last 3 months

    Hi,

    Are you confirming the formula with CTRL, SHIFT and ENTER? If correct, excel should display the formula within curly brackets {}.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    03-05-2020
    Location
    Ottawa, Ontario
    MS-Off Ver
    16.34
    Posts
    5

    Re: average weekday ongoing last 3 months

    yes, same result

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: average weekday ongoing last 3 months

    you have a range that is 366 columns wide, if you are recording in half days 3 months is about 180? How are you identifying the last 3 months?

  5. #5
    Registered User
    Join Date
    03-05-2020
    Location
    Ottawa, Ontario
    MS-Off Ver
    16.34
    Posts
    5

    Re: average weekday ongoing last 3 months

    The data is for a year - the 3 months will change each month - right now I'm wanting from Dec 2019 forward - that will change next month to Jan 2020 forward
    The data will all be added into Sheet1 each month, per time slot. I just want a way to average Mon-Fri 3 months (as Sat and Sunday are in the data as well)

  6. #6
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: average weekday ongoing last 3 months

    if B9 contains the date of 1st dec 2019 the below will work, but if you half 2 timeslots per date it may give an answer half of what you were expecting


    it is entered as an array with shift control enter

    =AVERAGE(IF((WEEKDAY(Sheet1!$B$2:Sheet1!$NC$2,2) < 6)*(Sheet1!$B$2:Sheet1!$NC$2 >=B9)*(Sheet1!$B$2:Sheet1!$NC$2 < EDATE(B9,3)),Sheet1!$B4:Sheet1!$NC4))

  7. #7
    Registered User
    Join Date
    03-05-2020
    Location
    Ottawa, Ontario
    MS-Off Ver
    16.34
    Posts
    5

    Re: average weekday ongoing last 3 months

    whoops think I clicked the wrong button to reply (sorry all new to this)

    I tried that formula and excel told me "there is a problem with the formula"
    I am going to try to cut my workbook down or take pictures or something to post as an example
    Last edited by EGagne; 03-05-2020 at 01:00 PM. Reason: pictures

  8. #8
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: average weekday ongoing last 3 months

    A workbook would be far easier than pictures

    Firstly you have blank cells, which would to be included in an average function but should be for your answer
    =sumproduct((WEEKDAY(Sheet1!$B$2:Sheet1!$NC$2,2) < 6)*(Sheet1!$B$2:Sheet1!$NC$2 >=B9)*(Sheet1!$B$2:Sheet1!$NC$2 < EDATE(B9,3))*(Sheet1!$B4:Sheet1!$NC4))/ sumproduct((WEEKDAY(Sheet1!$B$2:Sheet1!$NC$2,2) < 6)*(Sheet1!$B$2:Sheet1!$NC$2 >=B9)*(Sheet1!$B$2:Sheet1!$NC$2 < EDATE(B9,3)))


    Although if the date in the first place was in columns Date, time, volume, a pivot table would be far more efficient to get your results

  9. #9
    Registered User
    Join Date
    03-05-2020
    Location
    Ottawa, Ontario
    MS-Off Ver
    16.34
    Posts
    5

    Re: average weekday ongoing last 3 months

    Hi
    I tried the formula suggested, I get a pop up box "There's a problem with this formula".
    I'll have to try something else - maybe if I pull the data based on the text of each day
    I know a pivot table would get that data easy-peasy, but I was hoping for full automation

  10. #10
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: average weekday ongoing last 3 months

    I fear the number of formula calculations would make the sheet very slow

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. IF Then - using 12 months - ongoing average
    By harleypop in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2009, 01:22 PM
  2. [SOLVED] Adding an ongoing value to an Average
    By biggcheese in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2005, 04:05 PM
  3. [SOLVED] Adding an ongoing value to an Average
    By biggcheese in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  4. [SOLVED] Adding an ongoing value to an Average
    By Peo Sjoblom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 02:05 PM
  5. Adding an ongoing value to an Average
    By Peo Sjoblom in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2005, 09:05 AM
  6. [SOLVED] Adding an ongoing value to an Average
    By Peo Sjoblom in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 06:05 AM
  7. Adding an ongoing value to an Average
    By biggcheese in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  8. [SOLVED] Adding an ongoing value to an Average
    By biggcheese in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM

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