Average formula with GetPivotData

[DMfba]

Hi All,

I have been looking over some GetPivotData and Average solutions and can’t seem to find a way to correctly do what I need. I am attempting to create an Average formula that has a dynamic range using GetPivotData option. Is it possible for GetPivotData to return a range or a range of values?

I am trying to take the average of multiple months using GetPivotData depending on the current month. However, I am only able to point to one value in the Pivot table.

=AVERAGE(GETPIVOTDATA("Current Year Inventory",Sheet1!$V$5,"FY",2022,"Fiscal Month",Sheet1!$B$2))

For example, for FY 2022 and the month of Aug, I would like GetPivotData to return the range of F3:F4 and feed into the average formula. The next month I would like this formula to update and return the range F3:F5. The current month is referenced in Sheet1!$B$2.

Thank you for any suggestions

DM

 

[GraH]

Hi,

GETPIVOTDATA will return an intersect of the pivot. Meaning a Row/Column definition like: Sep for 2019 and it will return that value of 864,578$.
The formula you post returns the total for the year 2022, like you said 1 value only. And not the individual cell values on which you can calculate the average.
It's exactly returning what it is designed to do.

Why don't you simply use the aggregation Average in the pivot directly?

 

[DMfba]

Hi GraH,

Thank you for the reply. I am not able to use the aggregation because of the time frame I need the average for. It would only work for the 2022 year because there is no additional data. When I need to show the average for the same time last year it would be incorrect. I would need to show E3:E5 for 2021 and F3:F5 2022. I was hoping there would be a way to use the Getpivotdata to sum the cells I need and take the average.

The easy work around is to not use GetPivotData and just update the average formula as needed every month. I was trying to find a way around updating by hand.

Thank You,
DM

 

[GraH]

Would the grand Total of each column be incorrect when using average as aggregation? I'm guessing not.

 

[DMfba]

HI GraH,

For 2022 the grand total would be correct but for 2021 it would be incorrect. Since our fiscal year start in July, in this example I only have July - September to work with. For FY 2021, now that is in the past the grand total would include all 12 month. This would give the wrong total. For the Avg, I am comparing the 1st 3 months of the current fiscal year to the 1st 3 month of last fiscal year. If I use the grand total it would compare the 1st 3 month of 2020 to the total 12 months of 2021. Which is why I was looking for a way to make a dynamic range for the pivot table. As each month passes I would increase the average formula by 1 month for 2022 and 2021.

 

[GraH]

Then the year in your data is not the fiscal year? Yet your field names suggest fiscal periods.

 

[DMfba]

Thank You.