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.
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!
Read more IBM Planning Analytics Tips & Tricks:
IBM Planning Analytics Tips & Tricks: The CellValueN Formula
IBM Planning Analytics Tips & Tricks: Excel TYPE Function
IBM Planning Analytics Tips & Tricks: Excel Date Function Updated
