Average of a sheet summary field across two sheets

jjesmith

I have an average calculating in Sheet A. Another average calculating in Sheet B. These averages are calculated in the Sheet Summary currently. I'd like to create an average of these two averages to appear on a dashboard. I don't believe I can do calculations in a report document. I can have this calculation done in either Sheet A or Sheet B. It doesn't really matter. Or is it better to create a third Sheet just for this calculation? I'm lost on how to reference another sheet in the formula.

=Average(Average from Sheet A + Average from Sheet B)

Result needs to be able to be populated on a dashboard.

Darren Mullen

@jjesmith You can't pull in summary fields from another sheet. You can however pull in the range that you want to average from another sheet. You can do this into a sheet summary field if that helps then you'd have both averages in summary fields on the same sheet.

Andrée Starå

Hi @jjesmith

I hope you're well and safe!

You could average it in the Report with the Group/Sum feature.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

jjesmith

@Andrée Starå I do have two reports that include the two Averages that I want to average (one from Employee, one from their Supervisor). I could create a third report that bring in the Average data from each of those two reports. But then how do I average those two numbers on that third report? I don't understand how Group/Sum would work. It summarizes the data but doesn't calculate a new average.

Paul Newcome

@jjesmith You can create a Summary Report (instead of a row report) and pull in both sheets. Include that field then use the grouping/summary to get your average.

Andrée Starå

@jjesmith

You'd use a Summary Report instead referencing the sheets on which you'd like to calculate the average.

Make sense?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

jjesmith

@Darren Mullen thank you. that's good to know. I created a new column in one of the sheets to calculate the average from it's sheet and the average from another sheet. I put it in the sheet summary too and linked that to the dashboard. Not sure if this is best method but it works.