# Find Average Hours of Last N Months for Employee

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

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

3. ## 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?

4. ## 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:
`Please Login or Register  to view this content.`

5. ## 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.``

6. ## 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. ## 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!

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

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