8 Replies Latest reply: Aug 10, 2014 12:12 PM by ryanbrown_6
ryanbrown_6 Level 1 (0 points)



i'm a massive newbie to using spreadsheets and set myself a task to learn a thing or two.


Using Numbers, I want to create a football league formula whereby I submit the score in two different cells and I can then generate a W/L/D value in a third cell. I'm sure this is embarrassingly easy...


Also, I want to reference a value in a cell in a different sheet, but none if the formulas I looked at seem to work - any ideas?!


im using the beta version, if that makes any difference?!



MacBook Pro, Mac OS X (10.5.8)
  • Jerrold Green1 Level 7 (29,955 points)



    These are basic spreadsheet operations which you will find simple as you come to know them better.


    Let's say that team A's score is in Column A and team B's score is in column B. In column C, you could write an expression that gives you the W/L/D result from team A's perspective if you write:


    =IF(A>B, "W", IF(B>A, "L", "D"))


    In English: IF A's score is greater than B's, then the result is "W", else IF B's score is greater than A's, then the result is "L", else the result is "D".


    To reference a cell in another Sheet, you just need to include the Sheet name in the cell reference, followed by a double colon. For instance, a reference to cell A1 in sheet Fred would be written: =Fred::A1.





  • t quinn Level 4 (3,795 points)

    Hi ryan,


    here is one approach:

    Screen Shot 2014-08-06 at 6.21.43 PM.png

    The formula in D2=IF(C2>C3,"win",IF(C2<C3,"loss","draw")


    Select both D2 and D3, grab the yellow dot and drag down to fill.

    Screen Shot 2014-08-06 at 6.52.28 PM.png

    Here is your stats table.

    B2=COUNTIFS(Table 1::$B,$A2,Table 1::$D,"=win")

    I dragged this across and then modified the formula in the losses and draws columns ("=loss" in losses, etc..)

    The way I constructed this formula will answer your question about referencing cells in other tables but we will reference columns.

    COUNTIFS will take a series of test values and conditions separated by commas.

    our first test values are column B in Table 1- click the column head. condition is that it equal A2- click there.

    type a comma

    next test values are Table 1::column D-click there.

    condition is "=win"

    When we drag this formula across we don't want the columns changing so make sure the reference is set to preserve column.

    Once you have adjusted the losses and draws you can select all three and fill down.



  • ryanbrown_6 Level 1 (0 points)

    Thank you to you both - really helpful and I've solved the WLD issue. Is there anything I can add so that it doesn't carry out the action until there is content within the cell? Ie. for un played fixtures I currently have a lot of D's.


    Jerry, I'm still struggling with the referencing. I am sure I'm doing something stupid, but have attached a screenshot.




    Quinn - I'm clearly not the first person to create a sports spreadsheet ! Yours seems far more organised than mine though!

  • t quinn Level 4 (3,795 points)

    Hi ryan,


    To get rid of those unwanted Ds wrap your formula in another IF. Jerry's favorite seems to be using the LEN function which tests for a character entry. It is sweet.

    So this formula




  • t quinn Level 4 (3,795 points)

    Oh ryan,

    From your screen shot I am wondering if you are on an iPad. It might be a good idea to update your profile.


    On a practical level, you can adress your referenceing issue like this (Numbers 3 on a Mac):

    type your =

    navigate to your target sheet and click in your target cell

    type the rest of your formula.


  • Jerrold Green1 Level 7 (29,955 points)



    Try simplifying your Sheet names. Just a few letters, no spaces or special characters like the slash. See if that helps.



  • ryanbrown_6 Level 1 (0 points)

    Quin - it worked! Thanks!


    Jerry, I'm still struggling with the referencing, but will try on my desktop over the weekend and let you know.


    Thank you both once again



  • ryanbrown_6 Level 1 (0 points)

    Solved! Thank you! Going through the iCloud on my laptop helped.


    Is it possible to do a COUNTIF of a collection of alternate cells? It seems that I can only do it if I select cells that are next to each other.