CODEWITHSUNDEEP
exceltimegraph
RogerS
RogerS

Reputation: 11

In Excel: How to built a graph with time as X, names as Y with multiples series?

I am looking for a way to make a specific graph in Excel and I can't find a solution in Excel or on the web.

I have data about an online training with people completing parts of a course at a certain time:

FullName Course TIME
Name-A Part 1 23/03/2022 10:38
Name-A Part 2 23/03/2022 12:07
Name-A Part 3 23/03/2022 16:55
Name-B Part 1 11/03/2022 15:14
Name-B Part 2 22/03/2022 12:08
Name-B Part 3 28/03/2022 16:06
Name-B Part 4 30/03/2022 14:55
Name-B Part 5 18/04/2022 08:13
Name-C Part 1 11/04/2022 15:25
Name-C Part 2 20/04/2022 13:50

I would like to have a specific graph of this data:

Do you have any idea on how it could be achieved?

All the best, R.S. Edit: The table does not appear as in the preview so i try to add a screenshot: Screenshot of the table

Upvotes: 1

Views: 375

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34390

So one way to visualise this as mentioned in the comments is to create a separate series for each person and show passing each part of the course as a vertical step:

enter image description here

It's based very loosely on this but I've set each day in the date range as the x-coordinates and used a lookup to transform the data in H2

=RIGHT(XLOOKUP($G2+TIME(23,59,59),FILTER($C$2:$C$11,$A$2:$A$11=H$1),FILTER($B$2:$B$11,$A$2:$A$11=H$1),0,-1))+(COLUMN()-COLUMN($G$1))*10

pulled down and across to give

enter image description here

Explanation

The data for the graph has dates spanning the times in the raw data for its x-coordinates (column G). I generated it manually but could have used Sequence in Excel 365.

There are three columns of y-values, H to J, generating a separate series for each person. The three lines are initially spaced out by 10 units based on the column number. In the formula above, the raw data is filtered by the person's name so the headers in columns H, I or J match the names in column A in the raw data. Xlookup is used with 'next smallest' match so where the date in column G is greater or equal to the date/time in column C it will return the corresponding course from column B. Because column C actually contains date/times, I have added almost 24 hours when matching the date in column G to make sure that a match is found if the day is the same, regardless of time. In a case like Name-A, where three courses are completed in the same day, this will automatically select the last one (Part 3). Then I take the right-hand character of the course name (which is a digit in the sample data) and add it to the relative column number multiplied by 10. If there is no match, Xlookup returns zero so you just get the initial value for each series (10, 20 or 30), otherwise the result will be an increase by one unit each time a course is passed. If you couldn't assume the last character of the course name was a digit, you would need a lookup to assign a number to each course name.

The data is then plotted on a scatter graph with points joined by straight lines. I had to adjust the x-axis manually to make the range correct and the labelling clearer.

This could be done without Excel 365, probably using Aggregate to get the highest row number with a condition on the name and date.

EDIT

I could have achieved the same result much more easily using Countifs to find how many courses had been passed by a certain person by a certain date:

=COUNTIFS($A$2:$A$11,H$1,$C$2:$C$11,"<="&$G2+TIME(23,59,59))+(COLUMN()-COLUMN($G$1))*10

This wouldn't have needed Excel 365. If you needed to give different courses different weightings, you could do this with a sumproduct and a lookup, also fairly straightforward.

Upvotes: 1

Related Questions