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!

Austin Urban JCI

The COLLECT() function would essentially bucket your data into the same categories as the report does, and then changing the function from AVG() to SUM() etc could replicate the summary metrics shown in the report groupings

Ashlyn

Thank you!

I'll have a go at playing around with this!

I appreciate your help :)