Runner's time generates error when divided by distance run regardless of the number format

I coach XC and want to divide my runners' times by the distance they run to get an "average mile time." I enter my times in column C and put `=C3/1.3` in column D, but I get this:

Error
Function DIVIDE parameter 1 expects number values. But '9:07.7' is a text and cannot be coerced to a number.

I've tried changing the format in column C to all different time formats, but nothing works. I have done this with Excel in the past, but Sheets doesn't seem to make this easy.

Rubén is correct you are entering values that don't match a valid number format so Sheets is interpreting them as text.

When you then perform your division calculation on the text Sheets tries again to interpret them as numbers but fails.

Step 1

This step is not necessary if you modify the durations manually. If it isn't practical to edit all the durations or you simply prefer to continue entering times the way you have been, the following modified formula will resolve the issue:

``````=("0:" &C3)/1.3
``````

To calculate a range of values with a single formula you could use an ARRAYFORMULA:

``````=ARRAYFORMULA(
IF(LEN(C3:C20),
("0:" &C3:C20)/1.3, ""))
``````

Step 2

Apply the following custom number format to the cell(s) with your formula for durations with milliseconds:

``````hh:mm:ss.000
``````

You can tweak the custom number format. In the table below, the only thing that is changed in each row is the custom number format.

1. The first column contains a text value representing a duration.
2. The first row shows the underlying number to which all the custom formats are applied.
Text Custom Format Pace (Duration ÷ 1.3) Duration (Pace × 1.3)
9:31.5 Decimal `0.005088141026` `0.006614583333`
9:31.5 [h]:mm:ss.000 0:07:19.615 0:09:31.500
9:31.5 mm:ss.000 07:19.615 09:31.500
9:31.5 m:ss.000 7:19.615 9:31.500
9:31.5 m:ss.0 7:19.6 9:31.5

Enter `00:09:07.7` as in Google Sheets, duration values should include hours.

You might also have to set the cell format to duration as Google Sheets might show durations as time by default.