How do I use IF to generate values

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

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.

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.

quinn

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

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 your =

navigate to your target sheet and click in your target cell

type the rest of your formula.

quinn

Ryan,

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

Jerry