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.
here is one approach:
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.
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.
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!
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.