Pulling Averages from a Report to another sheet (Current / Prior month formula)

Ashlyn

Hello,

I currently have a sheet where I am manually pulling information from a report.

From the Sheet I want to write a formula that automatically pulls information from the Report.

I want one column to have "Current Month", another with "Prior Month", and the last one "YTD". (I will insert a photo of what I have.

Is it possible to pull averages from a report to a sheet through a formula? And how do I write a formula that pulls in the current month and prior months?

Thanks! 😊

Austin Urban JCI

I don't believe you would be ale to pull the summary metrics from a report, but you could replicate the reports values using a formula (apologies if this is not what you want). In your final sheet which needs the average, you could use a COLLECT() function to gather all the data that relates to the month and then average it. It would look something like:

=AVG(COLLECT({Base Sheet Range YTD Column},{Base Sheet Range Month ASR Submitted Column},month_here,{Base Sheet Range Year ASR Submitted Column},year_here))

Then you change the "month_here" and "year_here" to the desired month + year. In your final summary sheet you could have month and year as columns and reference them in the formula like:

=AVG(COLLECT({Base Sheet Range YTD Column},{Base Sheet Range Month ASR Submitted Column},[Month]@row,{Base Sheet Range Year ASR Submitted Column},[Year]@Row))

The references to "Base Sheet Range" would be external cell references to the columns in question

Happy to go more in depth if needed!