Microsoft Office Tutorials and References

In Depth Information

**Finding Where It Is**

✓
ROW returns the row number of the first cell of Team_Scores. That row

number is 3.

✓
ROWS returns the number of rows in the named range. That count is 7.

Adding these two numbers is not quite right. A 1 is subtracted from that total

to give the last row (9). In this example, you need only COLUMN to get the

column number because it understood that the range’s second column is the

column of scores. In other words, you have no idea how many rows the range

has, so ROW and ROWS are both used, but you do know the scores are in the

range’s second column. This tells you that cell C9 contains the average score.

Now what?

Cell B16 contains an IF that uses the address to perform its calculation:

=IF(ADDRESS(ROW(Team_Scores) + ROWS(Team_Scores) -

1, COLUMN(Team_Scores) + 1)>100,”Great

Teamwork!”,”Try again”)

The IF function tests whether the average score is greater than 100. If it is,

then the “Great Teamwork!” message is displayed. This test is possible

because the ADDRESS, ROW, ROWS, and COLUMN functions all help give the

IF function the address of the cell where the average score is calculated.

Using ROW, ROWS, COLUMN, or COLUMNS is easy. Here’s how:

1. Click the cell where you want the results to appear.

2. Enter
=ROW(
,
=ROWS(
,=COLUMN(
, or
=COLUMNS(
to start the function.

3. Enter a reference or drag the mouse over an area of the worksheet.

4. Type a
), and press Enter.

Again, these functions are rarely used alone, but are almost always used in a

more complex formula as in the example just presented.

OFFSET

The OFFSET function lets you get the address of the cell that is offset from

another cell by a certain number of rows and/or columns. For example, cell

E4 is offset from cell B4 by three columns because it is three columns to the

right. OFFSET takes up to five arguments. The first three are required:

✓
A cell address or a range address:
Named ranges are not allowed here.

✓
The number of rows to offset:
This can be a positive or negative

number. Use 0 for no row offset.