Relative column references
I have a sheet with reporting periods as columns and each row having a separate metric. Please see image (the yellow part).
I want to calculate the variance with respect to the previous month for all the rows,
i.e. Jan-17 (Sales - Variance) = Jan-17 (Sales) - Dec-16 (Sales)
which in this case is 100 - 94 = 6!
But I want to be able to do it for 6 cells and not type the formula out in each cell separately.
I'm happy to try out any other approaches to this issue that anyone can suggest too.
Any help would be greatly appreciated!
Thanks in advance,
Praveen
-
I'd recommend pivoting the data using the GROUPSELECT function first. Then you could utilize the GROUP_PREVIOUS function in a later sheet to be able to reference an ordered list of the records and the previous one to it. With this data, you could easily subtract one column from the other. If required, you could use GROUPSELECT again to pivot the data back.
The approach of repeating the functions for cells avoids having to pivot the data first to use GROUP_PREVIOUS. Both options will work technically, it's just a matter of which is more efficient for your scale.
Please sign in to leave a comment.
Comments
2 comments