I'm not even sure if this is the right place to ask but I wonder if anyone can give me a steer?
I need to make a spreadsheet that I can use for my child’s badminton scores.
Essentially there are 5 players (A,B,C,D,E) playing doubles on one court.
There are 15 permutations
A+B vs C+D (E sits out)
B+C vs D+E (A sits out)
C+D vs E+A (B sits out)
D+E vs A+B (C sits out)
E+A vs B+C (D sits out)
B+C vs A+D (E sits out)
B+D vs C+E (A sits out)
D+E vs A+C (B sits out)
A+E vs B+D (C sits out)
A+B vs C+E (D sits out)
D+B vs C+A (E sits out)
C+D vs B+E (A sits out)
C+E vs A+D (B sits out)
A+D vs B+E (C sits out)
A+C vs B+E (D sits out)
I have recorded the scores for each game over the last year.
To win, a team must reach a score of 21 but be clear by 2 points so the winning amount can be higher if it’s a close game.
I would like to create some kind graphical representations of the data but to my shame I have been stumped.
I have tried to find something similar online but with little sucess.
Any help greatly appreciated.
Sorry I don't have the tables with me at the moment. I'm not sure they would help anyway, they're a bit of a mess.
Ideally I'd like to see the data in a variety of ways, bar charts, pie charts etc so we could see the scores and dates relative to one another.
We'd like to see who are the strongest teams or who has the most improvement. If there is individual data that can be gleened from this then all the better.
I'm an illustrator by trade and I think this has got the better of me, I'm not entirely sure why I agreed to do it!!
Maybe I should speak to an accountant?
I think this may be a good start:
The table on the left is titled "Games" and is where you enter the players (columns A, B, E, and F), the scores (columns H and I). Column C, G, and J may be hidden and are caluclation columns used in other tables.
For the table Games:
C2=IF(A2>B2, B2&A2, A2&B2)
This is shorthand for in cell C2 type (or copy and paste from here) the formula:
"=IF(A2>B2, B2&A2, A2&B2)" without the first and last double quotes
select C2, copy
select column C, now hold the command key and click cell C1 (to unselect), paste
select column C, copy
select column F, paste
J2=IF(OR(ISBLANK(H2),ISBLANK(I2)),"", "Team "&IF(H2>I2, "1", "2"))
select J2, copy
select column J, now hold the command key and click cell J1 (to unselect), paste
enter your games as needed in this table.
Now create a table like show in the top, right called "Players".
In the ID column (column A) enter the player ID (consistent with what was used in the "Games" table)
In the Name column (column B) enter the player name
C2=COUNTIFS(Games::A, A2, Games::J, "Team 1")+COUNTIFS(Games::B, A2, Games::J, "Team 1")+COUNTIFS(Games::E, A2, Games::J, "Team 2")+COUNTIFS(Games::F, A2, Games::J, "Team 2")
D2=SUMIF(Games::A, A2, Games::H)+SUMIF(Games::B, A2, Games::H)+SUMIF(Games::E, A2, Games::I)+SUMIF(Games::F, A2, Games::I)
select C2 and D2 and fill down
Now create another table called "Teams" as shown in the bottom right. enter the player combinations as shown:
B2=VLOOKUP(LEFT(A2, 1),Players::A:B, 2, 0)&" and "&VLOOKUP(RIGHT(A2, 1),Players::A:B, 2, 0)
C2=COUNTIFS(Games::C, A2, Games::J, "Team 1")+COUNTIFS(Games::G, A2, Games::J, "Team 2")
select B2 and C2 then fill down
Wow! Wayne, way to go!
Excuse me while I bow so low in admiration that I'm actually doing a forward roll.
I'll check this out when I get home and let you know how I get on.
The formula's look a bit intimidating but I will attempt to learn!
Really appreciate all you efforts, fantastic.
Many, many thanks
I am repeatdly using the same formula. so review:
countif() and its' related function countifs()
The function reference is accesible from the web if you use the menu item "Held > Formulas and Function Help". Here is the same linke:
Sorry for not coming back with my efforts sooner, loads to do my end!
I really appreciate all the work you have done to help me but I'm having trouble with the formulas
I get an error - every time. I'm not sure if I understand all the functions and their consequences.
I’ll paste the first table, hopefully it will show my errors.
If it’s too much trouble please don’t feel obliged to help, I’m sure I can ask a local excel (ugh!) expert.
Thanks for all your help all the same.
The example I provided assumed (and you should do it this way too) that the players were designated in the top right table and given IDs as shown. I can provide a modification to hide the IDs and present real names. You should use the IDs as I showed in my example.
Please implement the previous step first then insert two columns after column F and then two columns after column B.
C2=VLOOKUP(A2,Players::$A:$B, 2, 0)
select C2, fill to the right one cell
select C2 and C3 and fill down as needed:
select C2 and C3, copy
select I2 thru J2, paste
select I2 thru J2, and fill down as needed:
now you can hide columns as needed. If you need to show the NON-playing person we can also make that change.