Forum Discussion
How to chart bike race results in Excel
Hello. Trying to make a chart - basic line chart - of bike race results. Race time (Elapsed time) up the Y axis, finish place ranking across the X axis.
I know part of the problem might be the way Excel manages time fields. The time is formatted as hh:mm:ss.dec. It was entered as text and then formatted as time. It appears that it sees the elapsed time data points all as zeroes, and charts them as 0:00.0 despite what is displayed in the cells.
Thanks.
<< 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.
5 Replies
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.
