February 12, 2021
Hi everyone, this is my first post, so here's hoping...
I'm trying to incorporate a CAGR into my pivot table (see attachment) but unfortunately, I've failed miserably to get to grips with adding a calculated field to it.
Ideally, I want 2 CAGR's, one tracking the 2017-20 growth and then another tracking 2021-24 growth.
Any assistance is gratefully received!
July 16, 2010
You can't calculate CAGR in a calculated field because you need to be able to dynamically determine how many periods in the data and then reference the first and last values to perform the calculation. It's just not something that you can do with calculated fields or items, sorry.
I recommend you perform these calculations in a cell outside of the PivotTable by referencing the PivotTable using the RRI function.