I have a Google Sheet that is populated by a linked Google Form. I've added some extra columns to calculate extra values for each row that gets added. I want these columns to be automatically calculated when new rows are populated, so I'd like to use
Several of my simple formulas are working fine, but I can't get the
ArrayFormula syntax correct for a formula which uses
FILTER() to calculate the maximum value in a column for all the cells above the current cell.
Timestamp Name In or out? Shift start time 14:45:59 N Clock-in (start) 14:51:02 T Clock-in (start) 14:51:46 N Clock-out 2:45:59 PM 15:03:51 J Clock-in (start)
Single-cell version (for calculating D6):
My (incorrect) attempt at arrayFormula for calculating cells D2 to D12:
How can I tell the arrayFormula to increment the end cell of the filter range each time, but keep the starting row the same?
I'm also open to other approaches besides FILTER (I tried Vlookup too, but didn't have any more luck). What I ultimately seek is a formula that finds the latest "start" time, with the same name label as the current cell, occurring before the current cell's timestamp.
Simplified example spreadsheet: https://docs.google.com/spreadsheets/d/1bUvlDvNY3BaXQZwfCe4XyKw-jD5M3ELEf_yM9QJTglQ/edit?usp=sharing