Creating a graph in Excel to track scores and events along the way....

[brendan344]

Hi guys,

What im actually looking for is a way to track cricket scores and graphically show where wickets were lost along the way. Where:

'x' Axis - Overs
'y' Axis - Runs
and wickets represented at the point where they fell - eg 2.5 overs, 25 runs


I've searched the site and the only thread i could find remains unanswered:

http://www.mrexcel.com/forum/excel-questions/264913-line-chart.html

To give an example how this should look -

If only Napoleon had watched cricket

(One Day Cricket Match Run Rate)



I've been trying to do this in Excel 07 & its been driving me mad. Any pointers would be gratefully appreciated.

 

[sijpie]

What is exactly the problem? how to decide which runs fall in which over?

Perhaps if you can post an actual example of scores then it will make it easier. Because it was some time ago that i (thought) I understood cricket scores...

 

[brendan344]

i'm interested in the statistics of many games to see if there are patterns in the game. if i can plot one game - the plan is to overlay many on top afterwards... just struggling to plot the first !

im mainly looking at T20 games - where each team bats for 20 overs each. the winner is the team that scores the most runs in the 20 overs. teams have 10 wkts - representing each batsmen (think of it as a live if you will). im particularly interested in the relationship between the scoring rate & wkts and the best way to view this data (imo) is graphically. it really tells the story of a game v clearly.

i've attached an example, together with a "worm chart" - which represents the cumulative scores across the 20 overs together with a chart i've copied of the internet as an example. in this example northants set england roughly 170 to win (from 50 overs - an over being 6 balls). you can see around about the 25 over mark - england were ahead by maybe 10 runs (both having lost 4 wkts). then around the 30 over mark they lost a wicket - triggering a collapse and losing the remaining 6 wkts for about 20 runs (ouch). looks like in the end they were bowled out for 90 ish losing by 80 odd runs. im keen to try to replicate this with the attached data.

any help is much appreicated.

thanks in advance.

(struggled to attach a spreadsheet, but you can get the idea with the data on the link - graph at the bottom)

 

[sijpie]

But why can't you just plot cumulative runs vs overs for both teams?

Runs Cum Runs Overs TeamA TeamB TeamA TeamB 1 2 2 2 2 2 3 4 5 6 3 2 0 7 6 4 4 0 11 6 5 2 4 13 10 6 2 2 15 12 7 3 4 18 16 8 2 0 20 16 9 4 0 24 16 10 2 4 26 20 11 2 2 28 22 12 3 4 31 26 13 2 0 33 26 14 4 0 37 26 15 2 4 39 30 16 2 2 41 32 17 3 4 44 36 18 2 0 46 36 19 4 0 50 36 20 2 4 52 40 <colgroup><col span="5"></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[brendan344]

i will, but how do i represent the where the wickets fell ?

Northants - June 2011

(see fall of wickets underneath the table)

 

[sijpie]

are those the square markers on the lines? You can have a second column for each team for the fallen wickets. The value here should be the same as the runs. But only in those opens where the wicket fell. Then format that series to show only marker, no line.

	Runs		Cum Runs	Fallen Wickets
Overs	TeamA	TeamB	TeamA	TeamB	TeamA	TeamB
1	2	2	2	2
2	3	4	5	6	5
3	2	0	7	6		6
4	4	0	11	6
5	2	4	13	10	13	10
6	2	2	15	12
7	3	4	18	16	18
8	2	0	20	16		16
9	4	0	24	16
10	2	4	26	20		20
11	2	2	28	22	28	22
12	3	4	31	26		26
13	2	0	33	26	33
14	4	0	37	26
15	2	4	39	30
16	2	2	41	32	41
17	3	4	44	36		36
18	2	0	46	36
19	4	0	50	36		36
20	2	4	52	40

<tbody>
</tbody>

https://www.dropbox.com/s/4sfdr9dkaoev9d4/cricket.jpg

 

[brendan344]

V useful - thanks.

One last q for you. If i were to do this for say Teams A, B, C & D - is there anyway I can group A & B and plot versus C & D ?

The theory being A & B will be one game & C & D another. Then looking to get A & B in Green and C & D in red for example.

 

[sijpie]

I guess you could. Place to check is Ron de Bruin's website. look in CPearson.com Topic Index
what he can do with graphs...