Incorrect average speed in Numbers

Question

Level 1

0 points

Incorrect average speed in Numbers

I have a spreadsheet for keeping track of my bike ride distance and ride times. The sheet has a cell that sums the monthly distance and another that sums the monthly times. The formula in the cell for average speed is a simple distance cell divided by time cell. For the past four months this has consistently yielded an average speed of 11.x mph. Prior to my ride today the total time has been shown as x hrs, x mins, x secs. With today’s time added, the total time went over 24 hours, so the cell now shows 1 day, x hrs, x mins, x secs., but the average speed went to 279.9.

What is wrong, and how do I fix it?

iPad mini, iPadOS 18

Posted on Jul 31, 2025 5:59 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,336 points

Jul 31, 2025 7:36 PM in response to ndist

It is calculating miles per day now. Include the DUR2HOURS function in your formula to turn the duration into a decimal number of hours before dividing.

Reply

Answer 2

Badunit

Level 7

30,336 points

Aug 1, 2025 1:56 PM in response to ndist

=(AA34−32.03)÷DUR2HOURS(AB34)

Reply

Answer 3

ndist Author

Level 1

0 points

Aug 1, 2025 1:13 PM in response to Badunit

Here’s my formula: (AA34−32.03)÷AB34 AA34 is the cell for YTD distance, AB34 is YTD time.

The -32.03 in the formula is because sometimes I know the distance of a ride but not the duration and I’ll enter a distance but no duration in the sheet. To get an accurate average speed I want to subtract those distances from the yearly total cell.

I’ll try putting that DUR2HOURS function in the formula, but I need to know where.

Thanks

Reply