Microsoft Office Tutorials and References
In Depth Information
Using Numbers with OFFSET to Describe a Range
OFFSET enables you to specify a reference. It does not move any cell. It
does not change the selection. It is just a numeric way to describe a reference.
OFFSET can be used in any function that is expecting a reference argument.
Excel Help provides a trivial example of =SUM(OFFSET(C2,1,2,3,1)), which
sums E3:E5. However, this example is silly because no one would ever write
such a formula! If you were to write such a formula, you would just write
=SUM(E3:E5) instead. The power of OFFSET comes when at least one of the
four numeric arguments is calculated by the COUNT function or a lookup
In Figure 12.37 , you can use COUNT(A5:A99) to count how many entries are in
column A. If you assume that there are no blanks in the range of data, you
can use the COUNT result as the heightargument in OFFSET to describe the
range of numbers. Here ’ s what you do:
11. There is nothing magic about the reference, so write it as
22. Do not move the starting position any rows or columns from cell A5.
The starting position is A5, so you always use 0 and 0 for rows and
columns. Therefore, the formula is now =OFFSET(A5,0,0,
33. If you want to include only the number of entries in the list, use
COUNT(A5:A999) as the height of the range. The formula is now
44. The width is one column, so make the function
55. Use your OFFSET function anywhere you would normally specify a
reference. You can use =SUM(OFFSET(A5,0,0,COUNT(A5:A999),1)) or
specify that formula as the series in a chart. This creates a dynamic
chart that grows or shrinks as the number of entries changes.