Share via

Create chart of race results

Anonymous
2009-12-03T17:10:15+00:00

i have a excel spreadsheet of 950 finishers in a 5k race.

i am trying to create a chart showing a bell curve of the finish times.

i am unable to get the chart to work.

any ideas?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2009-12-05T20:26:51+00:00

Interesting, so had to do with the value vs. the axis units it sound like...weren't formatted as times for some reason.

Hmm, so I can tell you the way I'd do it<g>, may not be better ways, but this works. 

  • I'd first decide on how many points you want on the chart to create the bell curve line (5-6?)
  • Pick 5-6 open cells (say D1:D6 for sake of argument)..in those, I'd enter the times that I though would roughly correspond to the points on the curve.  So for example, in the middle two cells, I'd use 8 minutes and 11 minutes.
  • Then, in E1:E6, I'd do a =COUNTIFS(C1:C950,("=>"&D1),C1:C950,("<="&D2))...this would look through the results and return the number of times that fell within the range of numbers that you chose as for your bell curve chart.
  • Then, create the line chart from cells E1:E6....if it doesn't quite give you the bell that you're looking for, then all you have to do is adjust the values you put into D1:D6 and it will move the points of the chart up and down until you get it like you want.

Does that make sense?

Was this answer helpful?

10 people found this answer helpful.
0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2009-12-05T16:19:35+00:00

    i tried your suggestions and unfortunatly, they did not help.

    i looked around some more and made some progress.

    i changed the time to a decimal by using the formula "=(C2-INT(C2))*24"

    that helped, and i now have a valid chart

    what i am trying to achieve is a bell curve that shows the rate at which finishers come in;

    trying to show that the majority of finishers are between 8 - 11 minutes, some before and lots after.

    any ideas on this?

    Was this answer helpful?

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2009-12-04T18:48:06+00:00

    it really does nothing. just an empty chart.

    i think it is to do with the formating of the times.

    this column is formatted as "Time" and type "13:30"

    but it is really not "Time" as in time of day ??

    the X axis says "Time" - that is the first cell in the time column

    the Y axis starts a 0:00 and goes up in some odd order up to 21:36 and then back to 0:00

    the final finish time in the spreadsheet is 77:01

    the top (title) shows "950", there are 950 finishers.

    the chart is empty.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2009-12-04T01:17:04+00:00

    So, what specifically happens when you try to create the chart (i.e. what do you see vs. what do you want to see)?  Other question I have is how is the data set up in your spreadsheet?  Guessing that you have names in the first column and then times in the 2nd...what format are the times in?  One issue that first comes to mind is if those values are really showing as text (vs. times) in Excel, which would make it difficult to chart.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2009-12-05T00:27:33+00:00

    Yeah, my guess is the format as well...couple things to try:

    • Select the cells with the times and do a Replace (CTL+H) and find spaces and replace with nothing...that should strip out any extra spaces that may be helping make it text.
    • Another thing that helps convert number looking text to actual values is to typea "1" in an empty cell, copy it, then select all of the time cells and choose Paste Special and choose "Multiply".  I actually use this little trick relatively often when working with data that has come in as text...it will then take that text string and convert it to a number.
    • Finally, with the time cells selected, go up to Format Number and make that the number formatting is set to a Time format (vs. a General format).  You probably want to have a number format of [HH]:SS orsomething along those lines.

    That combination of steps will likely get you there, but let me know if it doesn't, since then the issue is likely more around the chart.  The one other thing to try if the above steps don't get you what you want is to select the two columns of data again and create a new chart, just to double-check.

    Hope that helps,

    Dennis

    Was this answer helpful?

    0 comments