I've a table with this format :

StartedTime | EndedTime | Speed

And I want to make a graph showing the speed over time. The thing is that I can have the same StartedTime and EndedTime for multiples rows (with different speed).

Do I need to compute by myself the values making a new table with a unique timestamp before making the graph or excel can do it for me ?

And what if I've got some row where StartedTime and EndedTime are crossing each other ? (example below :)

StartedTime | EndedTime | Speed
001         | 004       | 1
002         | 007       | 1

In this example the graph should show a speed of 1 between 1-2, 2 between 2-4 and 1 between 4-7. Is it possible ?

up vote 2 down vote accepted

A slightly different alternative is to create a table that calculates the speed for each second, and base your chart on that table.

If you create a table with TimeIndex and Speed columns, and in your speed column enter something like this in the top row, then copy down:

=SUMIFS($C$1:$C$9,$A$1:$A$9,"<="&F2,$B$1:$B$9,">="&F2)

enter image description here

  • Yeah I went this way by default I was wondering if there were a faster way. This formula is perfect thank you. – naurel Jan 7 '16 at 14:04

I am afraid this cannot be done.

Two columns in your data represent the beginning and end time of speed data, which is an interval. Excel charts work in a different way. Every cell (meaning every column of every row) must contain either ...

  1. Value data to report
  2. Dimension data to differentiate the values of the same row from values of another row.

Excel charts cannot interpret data in two columns as the beginning & ending of data in another column.

As you said, you should work your data to look like this:

StartedTime | Speed
001 | 1
002 | 1
003 | 1
004 | 1
002 | 1
003 | 1
004 | 1
005 | 1
006 | 1
007 | 1

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.