Forum Discussion
How to chart bike race results in Excel
<< and excel thinks it is delimited data >>
That's OK. Although I would make a copy of the data before doing the actions in Hans' post, because this action is a data conversion, not (just) a data formatting.
With the original data (or copied data, your choice) selected, click that Text to Columns icon, then click the Next button, and on the next page of the dialog check the Tab checkbox (and leave the other checkboxes unchecked). Click the Finish button, and the result should be time values (e.g., 2:40:50 AM, as seen in the Formula Bar).
At this point you can change the formatting on those time values as desired. You might use the Custom value "h:mm:ss.0" or "hh:mm:ss.0" (without the quotes).
I made up some data, calculated Rank values, etc., and charted it with cardinal numbers (Rank) and with ordinal numbers on the X axis (Rank & Name can also be used):
And of course if you want the X axis sorted by place, sort your rows by Rank.
Try the following:
- Select the column with time values.
- On the Data tab of the ribbon, click Text to Columns.
- Click Finish.
Does that make a difference?
- I did that, and excel thinks it is delimited data. I tried to convert to time format, but cannot determine what settings to use.
Data should be:
mm:ss.d or h:mm:ss.d
Which is how it appears in the display.<< and excel thinks it is delimited data >>
That's OK. Although I would make a copy of the data before doing the actions in Hans' post, because this action is a data conversion, not (just) a data formatting.
With the original data (or copied data, your choice) selected, click that Text to Columns icon, then click the Next button, and on the next page of the dialog check the Tab checkbox (and leave the other checkboxes unchecked). Click the Finish button, and the result should be time values (e.g., 2:40:50 AM, as seen in the Formula Bar).
At this point you can change the formatting on those time values as desired. You might use the Custom value "h:mm:ss.0" or "hh:mm:ss.0" (without the quotes).
I made up some data, calculated Rank values, etc., and charted it with cardinal numbers (Rank) and with ordinal numbers on the X axis (Rank & Name can also be used):
And of course if you want the X axis sorted by place, sort your rows by Rank.
- That worked. Thanks!
- Follow-up: I used the general format setting on the conversion. This worked for the times that were less than an hour. 46:24.4 seems to graph properly. But 1:07:03.9 is showing on the chart as 07:04.0
