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


--
Bob Saver
------------------------------------------------------------------------
Bob Saver's Profile: http://www.excelforum.com/member.php...o&userid=17513
View this thread: http://www.excelforum.com/showthread...hreadid=543061

Try something like this:

First, restructure your stats table as follows:

This table is in cells A1:G5
Match B1_Runs B1_Result B2_Runs B2_Result B3_Runs B2_Result
1 10 NOT OUT 0 DNB 25 OUT
2 15 OUT 22 OUT 0 DNB
3 35 OUT 12 OUT 17 OUT
AVGS 30 B1_AVG 17 B2_AVG 21 B3_AVG

Batsman 1's average
B5: =SUM(B2:B4)/COUNTIF(C2:C4,"OUT")

Copy that formula go D5 and F5

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Bob Saver" wrote:


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


--
Bob Saver
------------------------------------------------------------------------
Bob Saver's Profile: http://www.excelforum.com/member.php...o&userid=17513
View this thread: http://www.excelforum.com/showthread...hreadid=543061

Fantastic - that works perfectly. Thank you very much.

Bob


--
Bob Saver
------------------------------------------------------------------------
Bob Saver's Profile: http://www.excelforum.com/member.php...o&userid=17513
View this thread: http://www.excelforum.com/showthread...hreadid=543061