4 Replies Latest reply: Dec 16, 2013 6:53 AM by Hiroto
timobateman Level 1 Level 1 (0 points)

I've made a spreadsheet in Numbers 2.3 for keeping track of FIFA standings for a group. Because the scores are empty, the formulas think it’s a 0. This is a problem for the draw column since it thinks that the scores are equal. Is there a function I’m missing here that I can add to my Draw column (E) to ignore the cells if blank? I'm sorry if this is solved, but I've been looking and cannot find anything I can use or understand. Thanks for the help.

 

A link to the spreadsheet is here.

 

https://www.dropbox.com/s/vjmgi4keo7g4x7u/Numbers%20Test.numbers

 

Running Numbers 2.3 on Mavericks.

 

Thanks.

  • 1. Re: Missing a Formula for ignoring blank cells.
    Barry Level 7 Level 7 (29,180 points)

    Hi Tim,

     

    Interesting question!

     

    Here's a possible solution. It requires two auxiliary columns (which may and should be hidden) to be added to Table 1, and a new formula to count the draws in Table 1-1, column D:

    Table 1:

    Screen Shot 2013-12-15 at 12.26.15 AM.png

    Columns F and G are the auxiliary columns. The formulas below are entered into row 2, then filled down to row 6.

     

    F2: ="-"&B2&"-"&E2&"-"&IF(AND(LEN(C)>0,LEN(D)>0),"XXX-","")

     

    This constructs a string consisting of the names of the opposing teams (from columns B and E) plus a code ( XXX ) that indicates scores have been entered for both teams (ie, the game has been played).

     

    G2: =C=D

     

    This returns TRUE if cells in column C and D have identical contents, and FALSE if their contents differ. TRUE indicates a possible draw, FALSE that one or the other team has won.

     

    Table 1-1

    Screen Shot 2013-12-15 at 12.26.50 AM.png

    The formula below is entered in D2, and filled down to D5:

     

    D2: =COUNTIFS(Table 1 :: $F,"=*"&A2&"*",Table 1 :: $F,"=*XXX*",Table 1 :: $G,TRUE)

     

    Syntax for COUNTIFS is COUNTIFS(test-values,condition,test-values,condition,test-values,condition)

     

    There are two tests on values in column F:

    • Does it contain the team name for whom we are counting draws?
    • Does it contain the string "XXX" (Has the game been played?)

    There is one test of the values in column G:

    • Is the value TRUE? (Do both teams have the same score—including both having no score–?)

     

    If all three conditions are TRUE, the row (game) is added to this teams Draw count.

     

    Regards,

    Barry

     

    Message was edited by: Barry (Deleted original formula from T 1-1::D2, which I had stored here.)

  • 2. Re: Missing a Formula for ignoring blank cells.
    Hiroto Level 5 Level 5 (5,025 points)

    Hello

     

    I'd introduce an auxially column F in Table 1 to hold a 4-state flag to represent the relation of scores x, y in the row, i.e.,

     

        blank for x and y are blank

        -1 for x < y

        0 for x = y

        1 for x > y

       

    and use this flag to calculate the W, D, and L in Table 1-1 by using formulae as listed below. I'd also use SUMIFS() in calculating + and  - in Table 1-1 for better maintainablitily. Formulae for rows 3 through end are to be generated by filling down the forumlae in row 2.

     

     

    Table 1

    F2    =IF(LEN(C2&D2)=0, "", IF(C2=D2, 0, IF(C2>D2, 1, IF(C2<D2, -1))))
    

     

     

    Table 1-1

    D1    W
    D2    =COUNTIFS(Table 1 :: B, A2, Table 1 :: F, 1)+COUNTIFS(Table 1 :: E, A2, Table 1 :: F, -1)
    E1    D
    E2    =COUNTIFS(Table 1 :: B, A2, Table 1 :: F, 0)+COUNTIFS(Table 1 :: E, A2, Table 1 :: F, 0)
    F1    L
    F2    =COUNTIFS(Table 1 :: B, A2, Table 1 :: F, -1)+COUNTIFS(Table 1 :: E, A2, Table 1 :: F, 1)
    G1    +
    G2    =SUMIFS(Table 1 :: C, Table 1 :: B, A2)+SUMIFS(Table 1 :: D, Table 1 :: E, A2)
    H1    -
    H2    =SUMIFS(Table 1 :: D, Table 1 :: B, A2)+SUMIFS(Table 1 :: C, Table 1 :: E, A2)
    

     

     

    Hope this may help,

    H

  • 3. Re: Missing a Formula for ignoring blank cells.
    Barry Level 7 Level 7 (29,180 points)

    @ Hiroto,

     

    "Hope this may help,"

     

    I like it. Much cleaner with a single column and a four state flag, and with a consistent approach to the two sets of 'totals' formulas (W, D, L and +, - ).

     

    Regards,

    Barry

  • 4. Re: Missing a Formula for ignoring blank cells.
    Hiroto Level 5 Level 5 (5,025 points)

     

     

    Embarrassed to see silly typos in my post.

     

    auxially => auxiliary

    maintainablitily => maintainability

     

    Hands shivered...

     

    Regards,

    Hiroto