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,.
Search JabSto ::




Custom Search