8 Replies Latest reply: Feb 7, 2014 1:44 PM by Wayne Contello
Level 1

Dear all,

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.

Kind regards

Chris

#### All replies

• Level 6
iWork

Chris,

What do you want to see?  Can you post the table you have now?  You can selec tthe table, then paste here.

• Level 1

Hi Wayne,

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?

Thanks

Chris

• Level 1

Really, I think my stumbling block is how to input all the data effectively, not so much the charts which are pretty straightforward if the data is acceptable.

• Level 6
iWork

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

• Level 1

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

Chris

• Level 6
iWork

I am repeatdly using the same formula.  so review:

countif() and its' related function countifs()

sumif()

The function reference is accesible from the web if you use the menu item "Held > Formulas and Function Help".  Here is the same linke:

http://help.apple.com/functions/mac/4.0/

• Level 1

Hi Wayne,

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.

• Level 6
iWork

Secret,

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.