Need some help with complicated (for me) formulas

  • Thread starter Thread starter MikeB
  • Start date Start date
M

MikeB

Hi all, I have a spreadsheet in which I track some chess players. I
track who they have played, what color and what the outcome of the
match was (Win, draw, loss).

At the moment I then manually calculate how many times each player has
played each color and what their score is. I then use that information
to match them up with a player they have not played before. Now it
sometimes happen that not all players are there and then I match up
the unpaired players with one another. When this happens, it may
happen that they play one another again.

This image is a part of my spreadsheet: http://i46.tinypic.com/2urld3m.jpg

Can anyone figure out a formula that I can tack on at the end of each
row to count the number of W(hite) and B(lack) instances for that
particular player? Bear in mind that the formula has to accomodate the
possibility of >1 instance of this in a cell (as in cell 3H or is that
H3?).

Then also, I'd dearly love to be able to have a formula to count the
numbers and work out a score (3.5/7). Again, same caveat as above.

My Excel knowledge is way too limited. And Ideally I'd like to not
have to do this in VBA.

Thanks for even looking at this.
 
Going by the names running down your sheet, I assume that the first
blank column is T, so in T2 you could put W and in U2 put B, and then
put this formula in T3:

=COUNTIF(C3:S3,"*"&T$2&"*")

and this one in U3:

=COUNTIF(C3:S3,"*"&U$2&"*")

Then copy these down to row 19. This will give you in column T a count
of the number of White, and the number of black in column U. However,
this is not foolproof - if you have two W's or 2 B's in one cell, then
only one will be counted, so this will give you a distorted count.

I think it would be better if you were to amend how you record the
data. If you reserved the bottom of the grid (below the leading
diagonal) for players using black, and the top triangle for players
using white, for example, then you would not need to actually record
the letter of the colour. You could then just record the result in the
appropriate cell, and if there were 2 games between two players, you
could just separate them with commas, for example. To help the
scoring, you could use letters to stand for win, lose or draw.

Your results sheet would then look something like this:

1 2 3 4 5 6 7 8
Player1 x L
Player2 x D,L
Player3 W x W
Player4 x
Player5 x
Player6 L x
Player7 D,L x
Player8 x
etc

(I'm not sure how well this will look once posted). Here, player3
(black) beat player1 (white), player6 (black) lost to player 3
(white), and player7 (black both times) drew once and lost once to
player2 (white).

Perhaps this gives you something to think about.

Hope this helps.

Pete
 
Going by the names running down your sheet, I assume that the first
blank column is T, so in T2 you could put W and in U2 put B, and then
put this formula in T3:

=COUNTIF(C3:S3,"*"&T$2&"*")

and this one in U3:

=COUNTIF(C3:S3,"*"&U$2&"*")

Then copy these down to row 19. This will give you in column T a count
of the number of White, and the number of black in column U. However,
this is not foolproof - if you have two W's or 2 B's in one cell, then
only one will be counted, so this will give you a distorted count.

I think it would be better if you were to amend how you record the
data. If you reserved the bottom of the grid (below the leading
diagonal) for players using black, and the top triangle for players
using white, for example, then you would not need to actually record
the letter of the colour. You could then just record the result in the
appropriate cell, and if there were 2 games between two players, you
could just separate them with commas, for example. To help the
scoring, you could use letters to stand for win, lose or draw.

Your results sheet would then look something like this:

                1     2     3     4     5    6     7     8
Player1     x            L
Player2            x                               D,L
Player3     W           x                   W
Player4                          x
Player5                                  x
Player6                    L                    x
Player7             D,L                               x
Player8                                                         x
etc

(I'm not sure how well this will look once posted). Here, player3
(black) beat player1 (white), player6 (black) lost to player 3
(white), and player7 (black both times) drew once and lost once to
player2 (white).

Perhaps this gives you something to think about.

Hope this helps.

Pete
Click to expand...

Thanks.

I'll think about some of what you wrote. Not sure why W,L,D is better
than 1,0,1/2.
 
Back
Top