I produce yearly and career averages for my social cricket team. With the great help of one of your kind forumites, Peter SSs, some eight years ago (crikey time flies!) I managed to transfer 23 seasons of information on to an Excel Workbook which has proved an absolute lifesaver in terms of time saved and instant statistical analysis.
Although my Excel knowledge has improved in the intervening time, I am back looking for help having been stumped (forgive the cricketing pun) again.
I want to produce a page within my Workbook to analyse bowling performance. I have a similar page for the batsmen, which works well. In the example immediately below, I simply type in the batsman's name in the cell B1 and using a series of VLOOKUP formulae, it searches and returns the following data for every match played: the number in the order they batted (column E), how they were out (column F), runs scored (column G) and result of the match (column H). This enables me to keep cumulative stats on performance (columns M-P) as well as how they perform in each different spot in the batting order, in wins and losses, etc etc (row R onwards).
[/URL][/IMG]
I would like to do the same for the year-by-year bowling figures, having a performance page whereby inputting the name of the bowler at the top of the page Excel will return for me a game-by-game breakdown of how that individual has performed, which will then enable me to analyse their bowling performances in similar fashion.
Here is the problem, while the batsmen's statistics can be retrieved by a simple VLOOKUP formula directing to a worksheet from the corresponding season, retrieving the bowling figures is much more problematical.
To be located, the relative stats to be "cross-referenced" (i.e. looking across and down) and to add greatly to the level of difficulty the bowler's name value is spread across five merged cells beneath which are the actual values that I need to retrieve. (See columns R through V in rows 1 through 3 of the embedded picture below.
To clarify, in the example below, I would like to retrieve: bowler Ritchie (ringed in red, spread across five merged cells) played against Staplefield (ringed in pink) and bowled (ringed in blue) seven overs (O), bowled one maiden (M, that's an over without conceding a run for non-cricketers), conceded 16 runs (R) and took one wicket (W).
Can it be done, or am I asking too much? I would be more than happy to tweak the design of the table if necessary, but I have not even the vaguest understanding of Macros and would like to avoid them.
I am running Excel 2007 with Windows 7 64-bit. Thank you in advance for any suggestions.
[/URL][/IMG]
Although my Excel knowledge has improved in the intervening time, I am back looking for help having been stumped (forgive the cricketing pun) again.
I want to produce a page within my Workbook to analyse bowling performance. I have a similar page for the batsmen, which works well. In the example immediately below, I simply type in the batsman's name in the cell B1 and using a series of VLOOKUP formulae, it searches and returns the following data for every match played: the number in the order they batted (column E), how they were out (column F), runs scored (column G) and result of the match (column H). This enables me to keep cumulative stats on performance (columns M-P) as well as how they perform in each different spot in the batting order, in wins and losses, etc etc (row R onwards).
I would like to do the same for the year-by-year bowling figures, having a performance page whereby inputting the name of the bowler at the top of the page Excel will return for me a game-by-game breakdown of how that individual has performed, which will then enable me to analyse their bowling performances in similar fashion.
Here is the problem, while the batsmen's statistics can be retrieved by a simple VLOOKUP formula directing to a worksheet from the corresponding season, retrieving the bowling figures is much more problematical.
To be located, the relative stats to be "cross-referenced" (i.e. looking across and down) and to add greatly to the level of difficulty the bowler's name value is spread across five merged cells beneath which are the actual values that I need to retrieve. (See columns R through V in rows 1 through 3 of the embedded picture below.
To clarify, in the example below, I would like to retrieve: bowler Ritchie (ringed in red, spread across five merged cells) played against Staplefield (ringed in pink) and bowled (ringed in blue) seven overs (O), bowled one maiden (M, that's an over without conceding a run for non-cricketers), conceded 16 runs (R) and took one wicket (W).
Can it be done, or am I asking too much? I would be more than happy to tweak the design of the table if necessary, but I have not even the vaguest understanding of Macros and would like to avoid them.
I am running Excel 2007 with Windows 7 64-bit. Thank you in advance for any suggestions.
