+ Reply to Thread
Results 1 to 7 of 7

Find Average Hours of Last N Months for Employee

  1. #1
    Registered User
    Join Date
    09-02-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    Find Average Hours of Last N Months for Employee

    Hi all,

    I have a raw data set that provides employee number, the hours per month, and the year and month in question. This sample data is meant to provide the hours per month each employee worked. What i am trying to do is a lookup against an employee number, and search the Year & Month column and find the most recent three months. I formatted the Year & Month column as a number in an attempt to find the three highest values. From there, for these three highest values in Year & Month column, i would like to formula to look at the cells in Hours Per Month column and find the average hours per month.

    In my attachment for employee 1 i have highlighted 202104, 202103 and 202102 as the three highest (most recent) months. Therefore i have manually taken the average of the cells to the left (145,145,140) which gives me a result of 143.33. For employee 2 the three most recent year & month that we see is 202011, 202010, and 202009. Therefore in this example we look to the column to the left and take the average of 146,166, 167, resulting in 159.66.

    I have attempted to use a variation of SUMPRODUCT and LARGE functions, but i can't seem to get the formula to look at more than 1 column and my usage of these functions usually results in my averaging out the year and month column instead of the Hours Per Month column.

    Please let me know if i have not provided sufficient detail and i will be happy to elaborate.

    Kind Regards,
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Find Average Hours of Last N Months for Employee

    One way assuming the data is always sorted as your example.

    Put 1 in F4 and 2 in F5 so that the Employee Number is consistent

    Then in G4
    SUM(OFFSET($B$1,MATCH(F4,A:A)-1,0,-3,1))/3

    For future ref it's advisable to use proper date numbers rather than a code to represent a month. It doesn't matter here since your 'date' codes are numerically consistent.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    09-02-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    Re: Find Average Hours of Last N Months for Employee

    Hello,

    Thank you for your reply. I confirm i have it working on my end. I agree that using proper dates is preferred, but the system i pull the dates from is formatted as 2021-04 and i can never seem to update the format to a date. I imagine i am noobing out somewhere.

    Would you mind clarifying from this part of the formula on? I see the Match is looking up F4 to match the "1" against Col A. However i am not clear why a -1 for the row and -3 for the height. Is this tell the formula to start at the bottom, hence the importance of ensuring the data is sorted from smallest to la?


    MATCH(F4,A:A)-1,0,-3,1))/3

    Thank you for your help. I really appreciate it


    Edit: A question came to mind. I realized that sometimes some employees may not have data for 3 months. While i may exclude this anyway (because for my purposes it may not be useful to consider this data), but if for example an employee has 2 months worth of data, dividing the sum by 3 will give us an incorrect result. Can the formula be amended to count by the number of months if there are less than 3?
    Last edited by Ebiru2387; 05-17-2021 at 05:06 PM.

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Find Average Hours of Last N Months for Employee

    Pl see file. Note the change in F4 and F5.
    Even if you have data less than 3 months it works.
    In H4 then copied down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 05-17-2021 at 11:06 PM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Find Average Hours of Last N Months for Employee

    It can be solved with a typical AVERAGEIFS function
    It works for case that an emloyee has less than 3 months, also YearMonth can be in any order.
    With F4=1 or 2

    Please Login or Register  to view this content.
    Quang PT

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Find Average Hours of Last N Months for Employee

    Hi,

    As others have suggested Averageifs and Aggregate functions offer a solution.

    The 'old fashioned' method I mentioned could be extended by replacing the absolute value divide by 3 with a function to calculate it

    =SUM(OFFSET($B$1,MATCH(F4,A:A)-1,0,-3,1))/COUNTA(OFFSET($B$1,MATCH(F4,A:A)-1,0,-3,1))

    To answer your question, yes the Offset function identifies a range and starts by anchoring the cell which is offset by 15 rows from B1 and zero column. i.e. B16.

    The height of the range is 3 rows, in this case the -3 indicates the height is 3 rows ending with B16.

    The COUNTA function identifies the number of non blank cells in the offset function.
    A lot of people don't like the Offset function since it's volatile and calculates whether it needs to or not every time a cell changes anywhere. In larger workbooks it does indeed slow stuff down noticeably. In many situations of course it's not noticeable at all and I personally find it's easier to read and work with.

  7. #7
    Registered User
    Join Date
    09-02-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    Re: Find Average Hours of Last N Months for Employee

    Hi all,

    Thank you all for your input. I found success using a couple of these formulas and thank you all for your feedback as it not only allowed me to resolve my problem, but learn further about the usage of these formulas!

+ 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. [SOLVED] Creating calendar formula and calulating employee hours and billable hours
    By ktps in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-05-2015, 08:13 PM
  2. Formula to find the employee and calcualte Hours worked
    By rizmomin in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-27-2014, 02:23 PM
  3. Code for looking in Column A find employee ID and pull total hours worked
    By prdponce in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-27-2013, 04:37 PM
  4. [SOLVED] Determine YTD Average Employee Weekly Hours
    By mycon73 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-22-2013, 08:08 PM
  5. Find and calculate employee hours within date range
    By rockytop80 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2011, 11:43 AM
  6. Calculate employee hours for employee evaluation?
    By Triesha in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2006, 10:55 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