Microsoft Office Tutorials and References

In Depth Information

**Finding Where It Is**

Formulas

Result

=ROW(D3)

3

=ROW(D3:G15)

3

=COLUMN(D3)

4

4

=COLUMN(D3:G15)

The first row of the Team_Scores range

=ROW(Team_Scores)

The first column of the Team_Scores range

=COLUMN(Team_Scores)

The ROWS and COLUMNS functions (notice these are now plural),

respectively, return the number of rows or the number of columns in a reference:

Formulas Result

=ROWS(Team_Scores)
Number of rows in the Team_Scores range

=COLUMNS(Team_Scores)
Number of columns in the Team_Scores range

Now you are getting somewhere. You can use these functions with ADDRESS

to do something useful. Here’s the scenario: You have a named range in

which the bottom row has summary information, such as averages. You need

to get at the bottom row but don’t know the actual row number. Figure 14-8

shows this situation. The Team_Scores range is B3:C9. Row 9 contains the

average score. You need that value in a calculation, even if another team is

added to the list and the row number changes.

Figure 14-8:

Using

reference

functions to

find a value.

Cell B12 uses a combination of ADDRESS, ROW, ROWS, and COLUMN to

determine the cell address where the average score is calculated. That

formula follows:

=ADDRESS(ROW(Team_Scores) + ROWS(Team_Scores) - 1,

COLUMN(Team_Scores) + 1)