Forum Discussion
Average Hours over last year - multiple job rows
I copied your data to Excel--some of the dates needed to be re-entered to conform with Excel's date formats.....
And then used this formula to yield the results shown below.
=AVERAGE(FILTER(Table1[Gross Hours],(Table1[Employee Id]=G4)*YEAR(Table1[Effective Date])=2022))
It would be easy to add the end date into the calculation as well (FILTER accommodates multiple criteria), but I don't really know what I'm looking at with your data.
It's hard to know what they mean because your sample data are--pardon me for saying it--odd. For example, employee ID 4 has gross hours of 2080 in each of the three rows, regardless of the dates associated. That may be accurate, but it makes it hard to test a formula to know if it's yielding useful results.
Anyway, the FILTER function does require Excel 2021 or newer. That link will take you to a good explanation of the function.
mathetes Thank you very much. Re the data - each line is showing a role change -so some will be hours some will be wages, some job titles etc
- I assume you're saying that the solution I gave worked. I'd appreciate, if it IS finished to your satisfaction, if you clicked on the "Mark as best response" link, so as to close it down,