IBM Planning Analytics Tips & Tricks: Excel OFFSET Function

February 22, 2022

Have you ever wanted to reference a cell based on its placement compared to another cell? Maybe you want to create a formula where each cell looks at the value 2 columns to the right or maybe you want to compare a value to the cell above. This information can be determined using Excel’s OFFSET function.

The OFFSET function is used to reference a range that is a specified number of rows and columns from a cell. The syntax of the function is:

=OFFSET (reference_cell, rows, columns)

Reference_cell
- This defines the source (e.g., starting) cell
Rows
- This defines the number of rows to “move” away from the reference cell. A positive number defines rows below and a negative number defines rows above.
Columns
- This defines the number of columns to “move” away from the reference cell. A positive number defines columns to the right and a negative number defines columns to the left.

The value associated with both the rows and the columns can be a hard coded number or it can be a calculated value. The formula can also be used to return a range of cells.

For example, the formula =OFFSET(B2,1,-1) would result in the value associated with cell A3 … which is one row below B2 and one column to the left of B2.

IBM Planning Analytics Tips & Tricks: Excel OFFSET Functions

I often use this formula in conjunction with conditional formatting when trying to “hide” values in stacked rows. For example, I can avoid having repeated values on the outside of my stack by creating a conditional format which states: IF the value of the current cell is the same as the value of the cell above THEN set the formatting to white-on-white.

Using Excel’s OFFSET function will help enhance your user experience by making reports easier to read.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Author

Lee Lazarow

View all posts