June 14, 2025
Stephanie is running an ultra in Wisconsin this weekend, I wanted to track her progress and give myself an idea of her pace and when I could expect her to complete the next segment.
Here’s the numbers table I setup:
As each split comes in, I add her new pace and time and everything updates. The only tricky bit is that my “estimate time” calculations depend on the most recent pace value; I expect she’s going to slow down significantly over 100 miles. Here’s the formula I ended up using:
INDEX($'Actual Pace m/mi',MAX(IF(ISBLANK(Stephanie’s Progress::$C$2:$C$13),0,ROW(Stephanie’s Progress::$C$2:$C$13))))
Let’s break it down:
IF(ISBLANK(Stephanie’s Progress::$C$2:$C$13),0,ROW(Stephanie’s Progress::$C$2:$C$13))
If a value is blank, return 0, else return the row number.
INDEX($'Actual Pace m/mi',MAX(...))
Whatever the max row number is, use INDEX to fetch the value at that row.
Pretty straightforward! The only thing that made this so difficult is that there’s inconsistencies in the way that Numbers formulas handle named column references, which caused my IF statement to try to fetch values out of bounds of the column. Hence me needing to use Stephanie’s Progress::$C$2:$C$13 in a few places.