I’m struggling to find a way to show weighted averages in Google Sheets Scorecards. My data looks something like this:
| Period | Person | Metric1 | Metric2 | Ratio |
|--------|--------|---------|---------|-------|
| Q1 | Alex | 5 | 20 | 25% |
| Q1 | Sam | 8 | 25 | 32% |
| Q2 | Alex | 10 | 15 | 67% |
The Scorecard calculates the average Ratio as (25% + 32% + 67%) / 3 = 41%. But I need it to sum Metric1 (5 + 8 + 10 = 23) and divide by the sum of Metric2 (20 + 25 + 15 = 60), giving 23/60 = 38%.
Can Scorecards be set up to do this kind of weighted average? I want to be able to use filters for Period and Person too. Any help would be great!
Unfortunately, Google Sheets Scorecards don’t natively support weighted averages as you’ve described. They’re designed for simpler aggregations like sum, average, or count. For your use case, you’d need to create a custom calculation outside the Scorecard.
A workaround could be to add calculated columns to your data source that perform the weighted average calculation, then use these pre-calculated values in your Scorecard. This method allows you to maintain filtering capabilities.
Alternatively, consider using Google Data Studio (now Looker Studio) for more advanced reporting. It offers greater flexibility in calculations and visualizations, including the ability to create custom fields for weighted averages, which might be a better fit for your needs.
I’ve faced similar challenges with Scorecards in Google Sheets. From my experience, the built-in functionality is quite limited for complex calculations like weighted averages. What I’ve found to work well is creating a separate sheet for these calculations.
In this new sheet, you can set up formulas that pull data from your main sheet, perform the weighted average calculations, and then display the results. You can use SUMIF and SUMIFS functions to handle the filtering by Period and Person.
Once you have this calculation sheet set up, you can reference these cells in your Scorecard. It’s not as elegant as having it all within the Scorecard itself, but it’s a workable solution that gives you the flexibility you need. Just remember to update your calculation sheet if you make changes to your main data structure.
hey there samuel87! unfortunately scorecards aren’t great for weighted averages you could try adding a new column with the calculation you want, then use that in the scorecard. or maybe check out looker studio? it’s got more options for custom stuff like this. good luck!
you could try adding a new column with the calculation you want, then use that in the scorecard. or maybe check out looker studio? it’s got more options for custom stuff like this. good luck!