Finding the smallest or largest value in Excel is easy with the MIN and MAX functions. But if you want to locate the lowest and highest working times — and figure out who worked them and on which day — this guide takes it further.
Step 1: Identify the Minimum and Maximum Working Time
Start by calculating the lowest working time recorded during the week with the MIN and MAX functions.
Step 2: Locate the Lowest Time Among All Employees
Now we’ll figure out who had the lowest working time. We do this by comparing the lowest value to everyone’s working times. This article also explains what the double hyphen — does in formulas.
⚠️ Please note: Both the cell range and the entire logical test must be enclosed in parentheses.
Only one cell will return 1, which marks the minimum.
Step 3: Extract the Row Number
To get the row number of that minimum:
- Place the logical test inside an IF function
- Remove the
--so the test returns TRUE or FALSE - Use ROW to return the row number if the test is TRUE
- For all other values, return an empty string
""
Step 4: Adjust the Row Number
The ROW function returns the worksheet row number, but our table starts on row 5. So, subtract 4 to match the actual table row.
For example, if it returns 7, subtracting 4 gives us row 3 in our table.
Step 5: Concatenate All Results
We now combine the row numbers into a single cell using the CONCAT function.
Step 6: Extract the Employee’s Name
Now let’s plug that row number into the INDEX function:
- First argument: the list of employee names (A5:A13)
- Second argument: the row number we just calculated
- Third argument: not needed — we only have one column as first argument
Step 7: Find the Day with the Minimum Working Time
Want to know which day that minimum occurred?
- Replace ROW with COLUMN to get the column number
- Adjust it for your original table
- In INDEX, use the range with the day names
- Leave the second argument blank
- Use the column number as the third argument
Final Note: Locate Lowest Highest Values with Accuracy
This technique allows users to precisely locate lowest highest values and associate them with both employee and date through a combination of logical tests and dynamic referencing.
