What formulas do I need to use to create a results table

You can also use the following technique if you wish to hide the content of cells, e.g. F12:F17 on Barry's table.

1. Select the target cells
2. On [Cell] tab of Inspector pane, choose [Create Custom Format...] from "Data Format" pop-up.
3. Give [Name] (as you like) for the custom format, e.g. Blank
4. Choose [Type] depend on the cell contents.
5. Make [Custom Format:] field empty.
6. Click [OK] button to save the custom format.

With this custom format chosen, the cell always looks as empty whatever the cell value is.

# the value or formula will reappear and become editable when you try to edit the cell.

Once the custom data format is created, you can set it for other cells in the document.

Hi C'

What is recorded/ calculated in GF, GA and GD of the Results table?

How many points are awarded for a win? a draw? a loss?

Here's the table with a few games recorded and the GP, W, D, L resuts calculated:

I've used F12:F17 to code the win/loss/draw for played games into a three letter code calculated from the scores entered in columns C and E. Scores are assumed to be entered in the same order as the team names appear in the same row.

Formulas used to this point:

F12, filled down to F17:

IF(OR(LEN(C12)<1,LEN(E12)<1),"",IF(C12=E12,"DPD",IF(C12>E12,"WPL","LPW")))

The first IF keeps the cell 'blank' until there is an entry for the score in both of C12 and E12.

The part in bold returns the Draw string ("DPD") if both scores are the same, the 'left wins' string ("WPL" if the score on the left is greater than the score on the right, and the 'left loses' string ("LPW") if the score on the left is less than the score on the right.

Using wild card characters (*), COUNTIFS can read these key strings to determine the number of games played, and the number won, lost or drawn by each team. The formula, with variations, is entered in H4, I4, J4 and K4, edited to count the correct codes for each column, then filled down to row 7

H4: COUNTIFS($A$12:$A$17,$G4,$F$12:$F$17,"=*P*")+COUNTIFS($B$12:$B$17,$G4,$F$12:$F$17,"=*P*")

I4: COUNTIFS($A$12:$A$17,$G4,$F$12:$F$17,"=W*")+COUNTIFS($B$12:$B$17,$G4,$F$12:$F$17,"=*W")

J4: COUNTIFS($A$12:$A$17,$G4,$F$12:$F$17,"=D*")+COUNTIFS($B$12:$B$17,$G4,$F$12:$F$17,"=*D")

K4: COUNTIFS($A$12:$A$17,$G4,$F$12:$F$17,"=L*")+COUNTIFS($B$12:$B$17,$G4,$F$12:$F$17,"=*L")

Note that two counts are needed for each total—one for the appearances of the team name in column A and that team's status at the beginning of the key code, the second for the same team name in column B, and its status at the end of the key code.

More on receipt of replies to my two qestions above.

Regards,

Barry

Hi C'

Here are the formulas for GF, GA, GD and Pts, placed (on my table) in the indicated cells, then filled down.

L4: SUMIF($A$12:$A$17,$G4,$C$12:$C$17)+SUMIF($B$12:$B$17,$G4,$E$12:$E$17)

M4: SUMIF($A$12:$A$17,$G4,$E$12:$E$17)+SUMIF($B$12:$B$17,$G4,$C$12:$C$17)

N4: L4−M4

O4: SUM(5×I4,3×J4,1×K4)

Notes on the formulas to follow, later today.

Regards,

Barry

"Make [Custom Format:] field empty."

Hi To_Mi,

While this will hide the formula results in these cells, it's not a technique I'd recommend for this purpose.

As you've noted, the formula results will reappear if the insertion point is within the cell, offering a warning that there is content there and that the user is in a position to edit it.

Two concerns: The user must be paying attention, and may press a key before noticing

the appearance of the content.

The appearance of that content may prove distracting to the user, leading to other errors.

My own recommendation, assuming the current 'single table' format is to be maintained and a separation column is desired, would be to insert a new column after column F (click on any cell in column F, press option-right arrow) then hide column F for the appearance below:

When hidden, the formulas in column F would then be safe from normal incursion and accidental editing. An unintended tab stroke would move the focus into a genuinely empty cell (in the new column G), where editing would do no harm.

Note that Numbers automatically adjusts the formulas in columns I through P so that they continue to reference the correct cells on their own and the other side of the 'trench'.

Regards,

Barry

Is there now a way to save this document as a template for Future? So I have to "save as" each time?

"Save as Template ..." is availbe under "File" menu.

You'll be asked to choose whether saving as template file or putting it in `Template Chooser', which will come up on creating new document if you set so at "For New Documents" setting in [General] pane of [Preferences...]

Charlottie86 wrote:

I've copied them into my cells I14-17, J14-17, K14-17 and L14-17 respectively and they are not updating!

Did you replaced the cell references in the formula with the one on you sheet accordingly? For example,

Barry's formula :

H4: COUNTIFS($A$12:$A$17,$G4,$F$12:$F$17,"=*P*")+COUNTIFS($B$12:$B$17,$G4,$F$12:$F$17,"=*P*")

should be translated for yours as :

I14: COUNTIFS($A$12:$A$17,$H14,$F$12:$F$17,"=*P*")+COUNTIFS($B$12:$B$17,$H14,$F$12:$F$17,"=*P*")

"Thanks again Barry! They have worked perfectly!"

Happy to hear that.

As promised, notes of the formulas

L4: SUMIF($A$12:$A$17,$G4,$C$12:$C$17)+SUMIF($B$12:$B$17,$G4,$E$12:$E$17)

M4: SUMIF($A$12:$A$17,$G4,$E$12:$E$17)+SUMIF($B$12:$B$17,$G4,$C$12:$C$17)

Similar to the COUNTIFS formulas for the wins, losses and draws.

The first sums the number of goals recorded by the named team in the left column game scores, the part after the + sums the goals recorded for the same team when it is named in the right column.

The second is the same formula with columns C and E switching positions to sum the goals against that team.

N4: L4−M4

Sub tracts the goals against from the goals by the same team.

O4: SUM(5×I4,3×J4,1×K4)

Multiplies the point value of a win by the number of wins, ditto for draws, ditto for losses, and sums the results.

Note: the formula above were copied from the table before adding a new column as described in the post above. All single cell refereces (eg. G4, L4) will have incremented to reference one column to the right of their original position.

Regards,

Barry