I have a big table of entries (called table1) that has Employee shift records. The table has Employee IDs, shift start times, shift end times and the income they have.
I want to create a spreadsheet that allows a user to enter an enquiry time during the day and it will return the highest income at that time and the corresponding employee ID.
The attached image is an example of some sample data.
I tried using the following formula to calculate the max income but get a #REF error:
=MAX(INDEX(Table1[Income],SUMPRODUCT(([@[Enquiry Time]]<Table1[End Time])*([@[Enquiry Time]]>Table1[Start Time])*(ROW(Table1[Income])))))
I have no idea what’s wrong and how I would calculate the employee ID of the highest earning employee as well.
Is anyone able to help with this?