Popular posts from Glen Feechan's Not Just Numbers blog - The blog for those who know it's not just about the numbers. The content provides useful Excel tips, in easy to understand language. Glen develops spreadsheets for clients all over the world via needaspreadsheet.com as well as through his consultancy work.

Get * The 5 Excel features that you need to know* free, by subscribing to Not Just Numbers

**here**.

## Using Offset with the SUM function

I often use the offset function with the SUM function in situations where you there is the possibility of a row being inserted between the Total cell, and the last row you wish to include in the total.

As an example, if cell A5 had the formula, SUM(A1:A4), then if a row was inserted above row 5, then the formula would move to cell A6, but the formula would remain as SUM(A1:A4), thereby not including any figures that were entered in A5.

There are various ways around this, but 1 rather neat solution is to have in cell A5 the formula, SUM(A1:OFFSET(A5,-1,0)). Should a row be inserted above row 5, then the formula would move down to cell A6, but the formula would change to SUM(A1:OFFSET(A6,-1,0)), thereby retaining a sum of the whole expected range.

If row1 was a header row and row 5 a total row, then if you were worried about row insertions at the top of the range 2:4, then you could even use the formula, in cell A5, SUM(OFFSET(A1,1,0):OFFSET(A5,-1,0)) !

One must note, however, that using too many OFFSETs can slow down your worksheet. This is because the function is recalculated everytime something changes on the spreadsheet. Most functions are only recalculated when a cell that affects the function, either directly or indirectly, is changed.