1

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

Guillaume H.
  • 13
  • 5
  • To make it easier for us to help you, please provide more detail as to your data. I don't see how you get the results you post from the data you provide. Please read [How to Provide an Example](http://stackoverflow.com/help/mcve) – Ron Rosenfeld Dec 15 '16 at 13:30
  • Added a google sheet to help you understand @RonRosenfeld ;-) – Guillaume H. Dec 15 '16 at 13:52

2 Answers2

1

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.

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
maxhob17
  • 612
  • 3
  • 9
  • I just tried that, but I get an error. What do you mean by "use ctrl + shift + enter": when should I use that shortcut and what does it do? @maxhob17 – Guillaume H. Dec 15 '16 at 14:09
  • In Excel, instead of just Enter after typing your formula, use Ctrl + Shift + Enter, this creates an array formula as opposed to a regular one http://www.excel-easy.com/functions/array-formulas.html – maxhob17 Dec 15 '16 at 14:41
0

I believe, this formula would help (Google Sheets only):

=QUERY(QUERY(QUERY({A3:C8};"select 'avg', Col2*Col3, Col2 where Col1 like '/brochures.htm%'");"select Col1, sum(Col2)/sum(Col3) group by Col1");"select Col2 Label Col2 ''")

First query selects Col2*Col3, Col2 when conditions are met: Col1 like '/brochures.htm%'

Second query finds the weighted average of a filtered table

Third query gets only the resulting number from the second query

In your sample: https://docs.google.com/spreadsheets/d/1Q-ydRSOdoGN2wVj7Z4XWglz-0U7pL3_xMeyGdRB2K84/edit#gid=1380706652

The benefit of using this formula is that you have to reference the data only once, and also you have more setting tools with query formula.

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81