Forum Discussion
Calculating and adding time
I am trying to figure out how to automatically calculate time differences for a delayed racing start time.
I have:
a rating which provides a handicap correction factor: PHRF Tod
A Sec/ Mile correction number
A Total correction in Seconds (based on a distance)
This I figured out.
I have a set start time for the first entry at 11:00:00
I need to figure out the Start time for the delayed boats as shown in the diagram, but I don't know what formulas will do this.
This diagram is taken from a pdf document.
Do the formulas in the attached file return the intended result?
8 Replies
The #NAME? error is almost certainly because SCAN, VSTACK, and the # spill reference only work in Microsoft 365's dynamic array engine — if you're on Excel 2019/2016 or another older version, those functions won't be recognized.
Quick checks:
Confirm which Excel version you're running (365 vs. one-time purchase).
If the startTime array formula itself fails, the startTime# reference will cascade errors downstream.
For adding/removing rows without breaking things, convert your source data into an Excel Table (Insert > Table) — combined with dynamic array formulas, it'll auto-resize as rows change.
I ran into similar formula compatibility issues in the last 2 months. While adjusting script outputs for an online fitness calculator. Standardizing the formula ranges fixed it.
Let me know your Excel version if you'd like a non-array alternative formula.
Based upon OliverScheurich workbook but using 365 formulas.
delay = ROUND(distance*correction, 0) startTimeSec = 24*60*60*raceStart + delay# startTime = raceStart + delay#/(24*60*60) intervals = startTime# - DROP(VSTACK(raceStart, startTime#),-1) startNumber = SCAN(1, SIGN(intervals#), SUM)Do the formulas in the attached file return the intended result?
Hi All, I have the problem sorted out.... Thanks anyway!
till next time...
