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)
Search JabSto ::

Custom Search