Are you struggling to calculate the weighted average of filtered or visible data in Google Sheets? Look no further! In this article, I’ll share a handy workaround solution that will help you achieve this calculation seamlessly.
Why the Popular Options Fall Short
Before we dive into the workaround, let’s address why the popular options like SUMPRODUCT and AVERAGE.WEIGHTED fail to support excluding hidden rows in the weighted average calculation. This limitation can be quite frustrating when working with filtered data in Google Sheets.
The Workaround Solution
Fortunately, there is a workaround that involves leveraging a combination of SUBTOTAL and MAP array formulas to identify visible rows. By using a virtual helper column within the two popular options, we can overcome this challenge.
But first, let’s understand the regular weighted average calculation:
Weighted Average = Sum (Data_Point_Value Weight) / Sum(Weight)*
To illustrate this, let’s consider a scenario where we have procured different quantities of a product at different unit prices. We want to calculate the weighted average cost of the product per unit.
Based on the formula mentioned above, the output would be a weighted average of 1.60 USD.
Using Formulas in Google Sheets
In Google Sheets, we can use either the AVERAGE.WEIGHTED formula or the SUMPRODUCT formula to calculate the weighted average cost. Here are the formulas you can use:
Formula #1: AVERAGE.WEIGHTED
=AVERAGE.WEIGHTED(B2:B8,C2:C8)Formula #2: SUMPRODUCT
=SUMPRODUCT(B2:B8,C2:C8)/SUM(C2:C8)These formulas work perfectly when all rows are visible. But what if we want to calculate the weighted average of filtered (visible) data?
Calculating the Weighted Average of Filtered Data
Let’s say we have a table similar to the one above, and we want to exclude the last three purchases while calculating the weighted average cost of the product per unit. We can hide these rows using grouping, filtering, manual hide, or slicer options.
The problem is that the previous formulas won’t differentiate between visible and hidden rows, resulting in the same output. But fear not! There’s a way to omit hidden rows while calculating the weighted average in Google Sheets.
The first step is to identify the visible rows in the range. You can achieve this by using the following MAP and SUBTOTAL combination, which returns 1 for visible rows and 0 for hidden rows:
=MAP(C2:C8, LAMBDA(r, SUBTOTAL(103, r)))Now, let’s incorporate this technique into the earlier two formulas.
For the AVERAGE.WEIGHTED formula, use the following approach:
Syntax:
=INDEX(AVERAGE.WEIGHTED(B2:B8, C2:C8*MAP_SUBTOTAL_COMBO))Scroll up and compare this formula with the previous one, especially the highlighted part. You can see that we have multiplied the weight with the hidden row identifier (combo formula) to return 0 for hidden rows.
We can also apply this technique in the SUMPRODUCT formula:
Syntax:
=SUMPRODUCT(B2:B8, C2:C8, MAP_SUBTOTAL_COMBO)/SUMPRODUCT(C2:C8*MAP_SUBTOTAL_COMBO)And that’s it! Now you can calculate the weighted average of visible or filtered data in Google Sheets with ease.
Thanks for joining me in unraveling this secret Google Sheets hack. If you want to discover more tips and tricks, head over to Crawlan.com. Happy spreadsheeting!
