Creating Averages for unequal numbers of events

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

Bob

[Ron Coderre]

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

[Bob Saver]

Fantastic - that works perfectly. Thank you very much.

Bob