B
Bob Saver
Hello Excel Experts
Here's a problem for you.
We want to work out the batting averages for three batsmen. We want to
update the average for each batsman each time they play a game.
however, the batsmen do not play in every game. There scores might look
like this:
Match Batsman 1 Batsman2 Batsman 3
1 10 runs Did Not Bat 25 runs
2 15 runs 22 runs DNB
3 35 runs 12 runs 17 runs
Batsman one has played three games so his average would be sum of three
scores divided by three. However, batsman 2 only played 2 games so his
average would be sum of runs scored divided by two.
Question One - how can I create a formula that recognises how many
times a batsman has batted so that the average is calculated correctly?
Is there a way to count up the number of "scores" (or values) and then
divide the sum?
OK - I'm sure that was easy for you guys. Now it get's harder.
Occassionally a batsman is "Not Out". In other wairds he was still
batting at the end of the game. In this case, whilst he DOES have a
score, and we would add that score to the total number of runs he has
scored, we would not increase the number of times he has batted. He
wasn't out after all!
So taking the list above for example, imagine batsman one was "not out"
at the end of his first match.
He scored 60 runs in total, but his average is 30. We only divide his
score by 2 rather than three because he wasn't out in the first game.
In effect we treat his first and second innings as one match because he
wasn't out at the end of the first match. In effect in the second match
he is just "carrying on from the end of the first match".
That's a bit more tricky isn't it!
Can anyone crack this - I certainly can't.
In anticipation of some lively debate - many many thansk.
Bob
Here's a problem for you.
We want to work out the batting averages for three batsmen. We want to
update the average for each batsman each time they play a game.
however, the batsmen do not play in every game. There scores might look
like this:
Match Batsman 1 Batsman2 Batsman 3
1 10 runs Did Not Bat 25 runs
2 15 runs 22 runs DNB
3 35 runs 12 runs 17 runs
Batsman one has played three games so his average would be sum of three
scores divided by three. However, batsman 2 only played 2 games so his
average would be sum of runs scored divided by two.
Question One - how can I create a formula that recognises how many
times a batsman has batted so that the average is calculated correctly?
Is there a way to count up the number of "scores" (or values) and then
divide the sum?
OK - I'm sure that was easy for you guys. Now it get's harder.
Occassionally a batsman is "Not Out". In other wairds he was still
batting at the end of the game. In this case, whilst he DOES have a
score, and we would add that score to the total number of runs he has
scored, we would not increase the number of times he has batted. He
wasn't out after all!
So taking the list above for example, imagine batsman one was "not out"
at the end of his first match.
He scored 60 runs in total, but his average is 30. We only divide his
score by 2 rather than three because he wasn't out in the first game.
In effect we treat his first and second innings as one match because he
wasn't out at the end of the first match. In effect in the second match
he is just "carrying on from the end of the first match".
That's a bit more tricky isn't it!
Can anyone crack this - I certainly can't.
In anticipation of some lively debate - many many thansk.
Bob