**timobateman**Dec 14, 2013 10:59 PM

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.

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:

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

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.)