Time series charting

ozbroady

New Member
Joined
Aug 5, 2015
Messages
3
Hi..

Im hoping someone can help me with some help building a graph.

I have a spreadsheet which I have simplified down to two columns, one is date, and the other is a number between 1 and 10.

The number between 1-10 signifies a category of a particular event that happened that day. Some dates have more than one event.

I would like to graph the total number of occurrences of each category over a monthly period.

So, maybe a line graph with 10 lines, and then the amount of instances for that month on the Y axis or something like that? I would also settle for 10 individual graphs if that was my only option. I am somewhat a novice when it comes to excel - I would really appreciate if someone could help me.

A sample of the data is as follows.

Thanks.


[TABLE="width: 550"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]EVENT CATEGORY[/TD]
[/TR]
[TR]
[TD]25/04/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]25/04/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]25/04/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]25/04/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]25/04/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]27/04/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]27/04/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]28/04/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]29/04/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]30/04/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]30/04/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/05/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1/05/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1/05/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1/05/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1/05/15[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2/05/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]2/05/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3/05/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5/05/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]6/05/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]7/05/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]7/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9/05/15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12/05/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]12/05/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]15/05/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]16/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17/05/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]17/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]17/05/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]18/05/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]18/05/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]18/05/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]18/05/15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]18/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]20/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]20/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]21/05/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]21/05/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]22/05/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]22/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]25/05/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]25/05/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]25/05/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]25/05/15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]26/05/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]26/05/15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]27/05/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]28/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]29/05/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]30/05/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]30/05/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/06/15[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1/06/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1/06/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]2/06/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2/06/15[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2/06/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2/06/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2/06/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5/06/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]5/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5/06/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]6/06/15[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]6/06/15[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]7/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8/06/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]9/06/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]10/06/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]10/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]11/06/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12/06/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13/06/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]13/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13/06/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]14/06/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]15/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16/06/15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]16/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16/06/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]17/06/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]17/06/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]17/06/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]18/06/15[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]18/06/15[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]18/06/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]19/06/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]19/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]19/06/15[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]19/06/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]20/06/15[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]22/06/15[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]22/06/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]25/06/15[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]26/06/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]27/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]27/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]27/06/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]27/06/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]28/06/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]29/06/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]30/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]30/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]30/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]30/06/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/07/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2/07/15[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2/07/15[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]2/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5/07/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9/07/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]10/07/15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]10/07/15[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]10/07/15[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]11/07/15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]11/07/15[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]12/07/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]12/07/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]12/07/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]12/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12/07/15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]13/07/15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]13/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]13/07/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]14/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]14/07/15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14/07/15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]14/07/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]15/07/15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]15/07/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]15/07/15[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi and welcome to the MrExcel Message Board.

One thing you could try is a stacked bar chart. That would give you one bar per day. Each bar could have a different colour for each event category and the length of the coloured segments would show the number of events of that category. The total height of the bar would show represent the total number of events for that day.

To do that I reorganised the data. I used the Data menu to make a copy of unique date values then I aded a heading for each event category. I used the last two characters in a look up later.

Then I created a COUNTIFS formula for each cell. There are lots of other ways to transpose the data. The fist few rows of data looked like this. You just need to select the data and insert a stacked bar chart and you are done.


Excel 2013
EFGHIJKLMNO
1DATEEC01EC02EC03EC04EC05EC06EC07EC08EC09EC10
225/04/20152201000000
327/04/20151001000000
428/04/20150000100000
529/04/20151000000000
Sheet1
Cell Formulas
RangeFormula
F2=COUNTIFS($A$2:$A$157,$E2,$B$2:$B$157,RIGHT(F$1,2))
G2=COUNTIFS($A$2:$A$157,$E2,$B$2:$B$157,RIGHT(G$1,2))
H2=COUNTIFS($A$2:$A$157,$E2,$B$2:$B$157,RIGHT(H$1,2))
I2=COUNTIFS($A$2:$A$157,$E2,$B$2:$B$157,RIGHT(I$1,2))
J2=COUNTIFS($A$2:$A$157,$E2,$B$2:$B$157,RIGHT(J$1,2))
K2=COUNTIFS($A$2:$A$157,$E2,$B$2:$B$157,RIGHT(K$1,2))
L2=COUNTIFS($A$2:$A$157,$E2,$B$2:$B$157,RIGHT(L$1,2))
M2=COUNTIFS($A$2:$A$157,$E2,$B$2:$B$157,RIGHT(M$1,2))
N2=COUNTIFS($A$2:$A$157,$E2,$B$2:$B$157,RIGHT(N$1,2))
O2=COUNTIFS($A$2:$A$157,$E2,$B$2:$B$157,RIGHT(O$1,2))
 
Upvote 0
Hi RickXL.. Thankyou so much for your assistance. I think you are on to something here. Instead of the stacked bar chart I used a line graph and it is giving me pretty much precisely the chart I wanted. I now just need to find the quickest way to reorganise the data as you have done. I will replicate the instructions have gave - It all appears quite straight forward.

Thanks again.
Chris
 
Upvote 0
Hi RickXL.. Can I ask what option in the data menu gave you the unique dates? Im guessing it was some sort of consolidation command. And to propogate the event info - was that manual?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top