Skip navigation

Spreadsheet Confusion

281 Views 8 Replies Latest reply: Feb 7, 2014 1:44 PM by Wayne Contello RSS
SecretHero Calculating status...
Currently Being Moderated
Jan 30, 2014 5:39 AM

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

  • Wayne Contello Level 6 Level 6 (12,670 points)
    Currently Being Moderated
    Jan 30, 2014 5:55 AM (in response to SecretHero)

    Chris,

     

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

  • Wayne Contello Level 6 Level 6 (12,670 points)
    Currently Being Moderated
    Jan 30, 2014 7:24 AM (in response to SecretHero)

    I think this may be a good start:

     

    Screen Shot 2014-01-30 at 9.10.52 AM.png

     

    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

  • Wayne Contello Level 6 Level 6 (12,670 points)
    Currently Being Moderated
    Jan 30, 2014 7:40 AM (in response to SecretHero)

    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/

  • Wayne Contello Level 6 Level 6 (12,670 points)
    Currently Being Moderated
    Feb 7, 2014 1:44 PM (in response to SecretHero)

    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.

     

    Screen Shot 2014-02-07 at 3.40.41 PM.png

    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.

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.