Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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.
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, ""))
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.
| 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.
