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,
Bookmarks