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

function.

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

=OFFSET(A5,

=OFFSET(A5,.

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,

=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

=OFFSET(A5,0,0,COUNT(A5:A999),

=OFFSET(A5,0,0,COUNT(A5:A999),.

44. The width is one column, so make the function

=OFFSET(A5,0,0,COUNT(A5:A999),1)

=OFFSET(A5,0,0,COUNT(A5:A999),1).

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.