software

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 quot;scoresquot; (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 quot;Not Outquot;. 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 quot;not outquot;
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 quot;carrying on from the end of the first matchquot;.

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: www.excelforum.com/member.php...oamp;userid=17513
View this thread: www.excelforum.com/showthread...hreadid=543061Try something like this:

First, restructure your stats table as follows:

This table is in cells A1:G5
MatchB1_RunsB1_ResultB2_RunsB2_ResultB3_RunsB2_Result
110NOT OUT0DNB25OUT
215OUT22OUT0DNB
335OUT12OUT17OUT
AVGS30B1_AVG17B2_AVG21B3_AVG

Batsman 1's average
B5: =SUM(B2:B4)/COUNTIF(C2:C4,quot;OUTquot;)

Copy that formula go D5 and F5

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

XL2002, WinXPquot;Bob Saverquot; wrote:

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


Fantastic - that works perfectly. Thank you very much.

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