The OFFSET function
Returns a reference a number of rows and columns away from a starting cell, optionally a whole range of a given height and width. Wrap it in SUM or AVERAGE to work with that range.
Syntax
OFFSET(reference, rows, cols, [height], [width])- reference
- The cell to start from.
- rows
- Rows to move down. A negative number moves up.
- cols
- Columns to move right. A negative number moves left.
- height (optional)
- How many rows the result covers. Left out, the height of reference.
- width (optional)
- How many columns the result covers. Left out, the width of reference.
Examples
D1
=OFFSET(1A1,22,31)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | 95 | |
| 2 | Jan | 120 | ||
| 3 | Feb | 95 | ||
| 4 | Mar | 140 | ||
| 5 | Apr | 110 | ||
| 6 | May | 80 | ||
| 7 | Jun | 130 |
In plain words: Start at 1A1, move 22 rows down and 31 column right.
D1=SUM(OFFSET(B2,0,0,3,1))
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | 355 | |
| 2 | Jan | 120 | ||
| 3 | Feb | 95 | ||
| 4 | Mar | 140 | ||
| 5 | Apr | 110 | ||
| 6 | May | 80 | ||
| 7 | Jun | 130 |
D1=SUM(OFFSET(B1,COUNT(B2:B7)-2,0,3,1))
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | 320 | |
| 2 | Jan | 120 | ||
| 3 | Feb | 95 | ||
| 4 | Mar | 140 | ||
| 5 | Apr | 110 | ||
| 6 | May | 80 | ||
| 7 | Jun | 130 |
Questions
How do I sum the last three values in a growing list?
Count the values and step back from the end, as in =SUM(OFFSET(B1,COUNT(B2:B100)-2,0,3,1)). When you add a month, COUNT goes up by one and the range moves with it.
Why is OFFSET slow in big workbooks?
OFFSET recalculates after every change anywhere in the workbook. INDEX can usually build the same range without that, as in =SUM(B2:INDEX(B2:B100,3)).