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.
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
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.
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.