Create Dynamic Excel Formulas with the OFFSET() Function
Microsoft Excel offers many ways to work with and analyze your data. There are loads of functions to calculate and lookup data, charts to visualize, PivotTables to quickly summarize and much more. These features, and more, combine to make Microsoft Excel a very powerful data analytics tool. Using just one of the tools Excel has to offer is great, but Excel really shines when you start combining multiple features together.
BUILDING A DYNAMIC SUM FORMULA
I got an excellent question in my complete Microsoft Excel - Beginner to Advanced course the other day and want to share it with you.
The question asked how you could SUM a dynamic range of cells. There are times when the user wishes to SUM the first 5 cells in a range, other times when the first 7 cells are needed and so on.
In the above graphic, the user may want to SUM the first 5 sales amounts, other times the first 7.
We can do this by creating a SUM formula to include all the cells and then modify the formula to include only the cells need.
This works, but it involves manipulating the formula and isn't very user friendly.
OFFSET() TO THE RESCUE
Microsoft Excel includes a function called OFFSET().
OFFSET( range, rows, columns, [height], [width] )
Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
The typical SUM formula in an Excel worksheet, SUM's up a range of cells. If you want to change the range you will need to go into the formula and adjust the referenced range.
By combining the SUM function with the OFFSET you can create a dynamic range to be SUM'd. This technique of combining functions is called NESTING.
In the above example, the OFFSET() function is NESTED inside of the SUM().
Here, we can inform the SUM function to use the OFFSET function to determine which cells to include in the SUM.
OFFSET(C3, 0, 0, 5)
The above OFFSET informs the SUM function to start with the C3 cell, move 0 ROWS, move 0 COLUMNS, and to include 5 cells starting at C3 and moving down.
Now if the first 7 cells are needed in the SUM, the OFFSET function can be changed from HEIGHT = 5 to HEIGHT = 7.
DOWNLOAD THE EXERCISE FILE TO PRACTICE