Microsoft Office Tutorials and References

In Depth Information

**Using Numbers with OFFSET to Describe a Range**

Figure 12.37.

Figure 12.37.
Every argument except

Every argument except height

height is hard-coded in these functions.

is hard-coded in these functions.

The

The height

height argument comes from a

argument comes from a COUNT

function to allow the range to ex-

pand as more entries are added.

COUNT function to allow the range to ex-

pand as more entries are added.

For a more complex example of OFFSET, examine
Figure 12.38
, which shows

several yearly tables starting in cell C8. Each month of the table contains

from one to five entries. The person using this spreadsheet will select a year

and a month from cells E1 and E2. The goal is to find information about the

entries for that particular month and year. Here
’
s how you do it:

11. Have the formula in cell I1 find the starting row for the particular

year, using the MATCH function shown in cell J1.

22. Have the formula in cell I2 find the column for the chosen month, us-

ing the MATCH function shown in cell J2.

33. Build the OFFSET function to describe the range for that month and

year. You know that it starts in the rowin I1 and the columnin I2. If

you make the referencecell A1, then row 15 is 14 rows below A1.

Therefore, use =OFFSET(A1,I1-1,

=OFFSET(A1,I1-1,.