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

HI,

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?!

thanks!

MacBook Pro, Mac OS X (10.5.8)
• Level 7 (29,960 points)

Ryan,

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.

Regards,

Jerry

• Level 5 (4,244 points)

Hi ryan,

here is one approach:

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

D3=IF(D2="draw",D2,IF(D2="win","loss","win"))

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

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.

quinn

• 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!

• Level 5 (4,244 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

D2=IF(C2>C3,"win",IF(C2<C3,"loss","draw")becomes

IF(LEN(C2)>0,IF(C2>C3,"win",IF(C2<C3,"loss","draw")),"")

quinn

• Level 5 (4,244 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 the rest of your formula.

quinn

• Level 7 (29,960 points)

Ryan,

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

Jerry

• 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

Ryan

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