Search code examples
excelgoogle-sheetsweighted-average

Get a weighted average of a filtered table

I'm looking for a solution that would give me a weighted average based on a dynamically filtered table. Here is what I have:

Column A: a list of urls, Column B: numbers, Column C: percentages

/page                   100000  20%
/brochures.htm?en       27443   75,30%
/brochures.htm?fr       1656    67,33%
/brochures.htm?it       483     75,00%
/brochures.htm?fr-fr    6       0,00%

What I want, is a formula that give me the weighted percentage of the pages that start with "/brochures/htm", which is in this case: 74,83%.

I am able to do it in two times with an intermediate sheet where I filter the table with =FILTER(A1:C5;REGEXMATCH(A1:A5;"/brochures.html*")) then in the third sheet by calculating the weighted average with =SUMPRODUCT(C1:C5;B1:B5)/SUM(B1:B5) , but as you can imagine it is not a good solution for my needs.

Anyone can help? I've created a google sheet so you can easily understand how I get to that 74,83%. -> https://docs.google.com/spreadsheets/d/1Q-ydRSOdoGN2wVj7Z4XWglz-0U7pL3_xMeyGdRB2K84/edit?usp=sharing

Solution

  • For Excel. You could use an array formula to achieve this. Make sure to use Ctrl + Shift + Enter instead of just Enter after typing out the formula

    =SUMPRODUCT(IF(LEFT(A1:A5,14)="/brochures.htm",B1:B5,0),C1:C5)/SUM(IF(LEFT(A1:A5,14)="/brochures.htm",B1:B5,0))

    I tested this with your sample data above in Excel and it came out with 74.83%. I hope this helps

    For Google sheets this will work: =ArrayFormula(SUMPRODUCT(IF(LEFT(A1:A5,14)="/brochures.htm",B1:B5,0),C1:C5)/SUM(IF(LEFT(A1:A5,14)="/brochures.htm",B1:B5,0)))

    or use semicolon ; instead the coma , if your local setting are different from the US.