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

#### brendan344

##### New Member
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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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...

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.

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

Last edited:
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>

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

Northants - June 2011

(see fall of wickets underneath the table)

Last edited:
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

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.

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...

Replies
1
Views
311
Replies
6
Views
219
Replies
3
Views
276
Replies
0
Views
1K
Replies
5
Views
1K

1,196,515
Messages
6,015,661
Members
441,914
Latest member
VBAllTheThings

### 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.

### Which adblocker are you using?

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

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