Time series charting

[ozbroady]

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.

DATE	EVENT CATEGORY
25/04/15	2
25/04/15	1
25/04/15	4
25/04/15	2
25/04/15	1
27/04/15	1
27/04/15	4
28/04/15	5
29/04/15	1
30/04/15	2
30/04/15	1
1/05/15	5
1/05/15	5
1/05/15	5
1/05/15	5
1/05/15	10
2/05/15	7
2/05/15	4
3/05/15	2
5/05/15	5
6/05/15	7
7/05/15	5
7/05/15	1
9/05/15	3
10/05/15	1
12/05/15	7
12/05/15	2
12/05/15	1
13/05/15	1
14/05/15	1
15/05/15	7
16/05/15	1
16/05/15	1
17/05/15	1
17/05/15	7
17/05/15	1
17/05/15	2
18/05/15	5
18/05/15	5
18/05/15	5
18/05/15	3
18/05/15	1
20/05/15	1
20/05/15	1
21/05/15	7
21/05/15	4
22/05/15	2
22/05/15	1
25/05/15	4
25/05/15	7
25/05/15	7
25/05/15	3
26/05/15	5
26/05/15	3
27/05/15	5
28/05/15	1
29/05/15	7
30/05/15	5
30/05/15	1
1/06/15	1
1/06/15	10
1/06/15	4
1/06/15	7
2/06/15	5
2/06/15	10
2/06/15	2
2/06/15	5
2/06/15	4
2/06/15	1
3/06/15	1
3/06/15	1
5/06/15	7
5/06/15	1
5/06/15	1
5/06/15	5
6/06/15	8
6/06/15	6
7/06/15	1
7/06/15	1
8/06/15	1
8/06/15	4
9/06/15	7
10/06/15	4
10/06/15	1
11/06/15	2
12/06/15	2
13/06/15	7
13/06/15	1
13/06/15	4
14/06/15	7
15/06/15	1
16/06/15	1
16/06/15	1
16/06/15	3
16/06/15	1
16/06/15	5
17/06/15	7
17/06/15	7
17/06/15	2
18/06/15	10
18/06/15	8
18/06/15	5
19/06/15	5
19/06/15	1
19/06/15	6
19/06/15	7
20/06/15	6
22/06/15	6
22/06/15	7
25/06/15	10
26/06/15	2
27/06/15	1
27/06/15	1
27/06/15	4
27/06/15	4
28/06/15	7
29/06/15	7
30/06/15	1
30/06/15	1
30/06/15	1
30/06/15	1
1/07/15	5
2/07/15	10
2/07/15	6
2/07/15	1
3/07/15	1
5/07/15	5
5/07/15	1
5/07/15	1
6/07/15	1
7/07/15	1
8/07/15	1
9/07/15	1
9/07/15	1
9/07/15	7
10/07/15	3
10/07/15	1
10/07/15	9
10/07/15	8
11/07/15	3
11/07/15	10
12/07/15	7
12/07/15	5
12/07/15	5
12/07/15	1
12/07/15	3
13/07/15	3
13/07/15	1
13/07/15	7
14/07/15	1
14/07/15	3
14/07/15	5
14/07/15	2
15/07/15	7
15/07/15	1
15/07/15	5

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[RickXL]

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
	E	F	G	H	I	J	K	L	M	N	O
1	DATE	EC01	EC02	EC03	EC04	EC05	EC06	EC07	EC08	EC09	EC10
2	25/04/2015	2	2	0	1	0	0	0	0	0	0
3	27/04/2015	1	0	0	1	0	0	0	0	0	0
4	28/04/2015	0	0	0	0	1	0	0	0	0	0
5	29/04/2015	1	0	0	0	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
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))

 

[ozbroady]

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

 

[ozbroady]

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?

 

[RickXL]

Hi,

Go to the Data tab and look for Remove Duplicates.