How to calculate weighted averages in Google Sheets?

Question

I have a Google Sheets where products are listed as rows and attributes as columns. Each product's attribute is rated on a scale of 1-10. My last column is an average of these values (i.e. =Average(B2:D2)). This works fine if each attribute has the same weight.

+--------+-------+-------+-------+---------+
|        | Attr1 | Attr2 | Attr3 | Overall |
+--------+-------+-------+-------+---------+
| Prod 1 | 10    | 8     | 9     | 9       |
| Prod 2 | 2     | 10    | 7     | 6.33    |
| Prod 3 | 4     | 6     | 6     | 5.33    |
+--------+-------+-------+-------+---------+

The problem is that I want each attribute to have a different weight. For example, Attr1 might not be important and should only be worth 50%, while Attr3 is very important and should be worth 300%.

+--------+-------------+-------+--------------+---------+
|        | Attr1 (50%) | Attr2 | Attr3 (300%) | Overall |
+--------+-------------+-------+--------------+---------+
| Prod 1 | 10          | 8     | 9            | 8.89    |
| Prod 2 | 2           | 10    | 7            | 7.11    |
| Prod 3 | 4           | 6     | 6            | 5.78    |
+--------+-------------+-------+--------------+---------+

The value for the first row would be:

(10*0.5 + 8*1 + 9*3) / (0.5+1+3) = 8.89

which could be calculated by using:

(
  B2*(IFERROR(REGEXEXTRACT(B1, "\d+"), 100)/100) 
  + C2*(IFERROR(REGEXEXTRACT(C1, "\d+"), 100)/100)
  + D2*(IFERROR(REGEXEXTRACT(D1, "\d+"), 100)/100) 
) / (
  IFERROR(REGEXEXTRACT(B1, "\d+"), 100)/100
  + IFERROR(REGEXEXTRACT(C1, "\d+"), 100)/100
  + IFERROR(REGEXEXTRACT(D1, "\d+"), 100)/100
)

which, as you can see, can become very difficult to manage as more attributes are added. Ideally, I'm looking for a solution that doesn't require creating temporary cells to help with the calculations.

Is there any built in function or a common convention that can help me calculate these weighted averages?

Lipis · Accepted Answer · 2010-12-25 13:07:47Z

There is no built in function to calculate the weighted average so you have to write a custom function if you want to avoid using too many temporary cells. So here is how you can achieve your goal.

Go to Tools > Scripts > Script editor..., Copy/Paste the bellow code and save it:

function weightedAverage(v, w) {
  var sum_v = 0;
  var sum_w = 0;

  if (v[0].length != w[0].length) {
    return "#ERROR: Incorrect number of values and weights";  
  }

  for (var c = 0; c < v[0].length; ++c) {
    sum_v += v[0][c] * w[0][c]; 
    sum_w += w[0][c]; 
  }

  return sum_v/sum_w;
}

Use it like:

=weightedAverage(B3:D3,$B$2:$D$2)

Where B3:D3 are your values and $B$2:$D$2 your weights. It's not error proof (the only check is to make sure both arrays have the same length) but it will do the trick.

In the above example I'm not trying to extract the weights from the title of the attribute, but I'm reading them from the second row (B2:D2) to make our life easier and clearer. The $ is not changing the result of the formula. It's only affecting what's happening when you're copying the formula in another cell. The part of the cell reference following the $ will not change (link for more details). Write the formula once in the cell E3 and Copy it to the rest of the rows to see it in action.

@Senseful I updated my answer, and you can Google in general for the dollar sign in excel it's the same thing since you'll find more documentation for it. — Lipis, Dec 25 '10 at 13:17
Your function doesn't work. But this works for me: gist.github.com/totty90/b58f47dbc430a53d2e5b — Totty.js, Apr 30 '15 at 8:38
There's another higher rated answer on this page that works and uses the built in function sumproduct — Brad Parks, Feb 1 '17 at 12:47

ale · Accepted Answer · 2012-09-28 00:33:28Z

58

If your weights are in row 2 from B to L, and the data you want to average is in rows 3 and higher, say, then you can use sumproduct as follows:

=sumproduct(B$2:L$2, C3:L3)/sum(B$2:L$2)

edited Sep 28 '12 at 0:33

ale

43.6k25 gold badges119 silver badges263 bronze badges

answered Aug 10 '12 at 22:48

Brendan Murphy

5894 silver badges2 bronze badges

7

+1 this answer is simple, working and much better than the accepted one. – Waqar Lim Sep 30 '13 at 8:56

add a comment |

ale · Accepted Answer · 2018-02-09 17:38:25Z

6

Google seemed to have listened. At least in my instance of Google Sheets, the function AVERAGE.WEIGHTED exists. Per the help:

=AVERAGE.WEIGHTED(values,
                  weights,
                  [additional_values, ...],
                  [additional_weights, ...])

edited Feb 9 '18 at 17:38

ale

43.6k25 gold badges119 silver badges263 bronze badges

answered Feb 9 '18 at 16:41

Tony Shouse

611 silver badge2 bronze badges

Link to help please. Could not find. – paragbaxi Jul 5 '18 at 16:56
support.google.com/docs/answer/9084098 – Adriano P Feb 16 at 2:29

add a comment |

Zhenya Morozov · Accepted Answer · 2016-01-26 09:13:05Z

Lipis' code was not working for me, so here's an update. This code:

works with current version of Google Spreadsheets correctly addressing array elements
checks whether the values are numbers
has a switch to consider zero values or not

Code:

/**
  v - Values range
  w - Weights range
  z - count zero values?
*/

function weightedAverage(v, w, z) {
  var sum_v = 0;
  var sum_w = 0;

  if (v.length != w.length) {
    return "#ERROR: Incorrect number of values and weights";  
  }

  for (var c = 0; c < v.length; ++c) {
    if (!z && Number(v[c][0])!=0) {
      sum_v += Number(v[c][0]) * Number(w[c][0]); 
      sum_w += Number(w[c][0]); 
    }
  }

  return sum_v/sum_w;
}

Sai · Accepted Answer · 2012-03-01 17:39:05Z

0

Correcting Lipis' code to work for the current version of Google Spreadsheets:

function weightedAverage(v, w) {
  var sum_v = 0;
  var sum_w = 0;

  if (v.length != w.length) {
    return "#ERROR: Incorrect number of values and weights";  
  }

  for (var c = 0; c < v.length; ++c) {
    sum_v += v[c] * w[c]; 
    sum_w += w[c] * 1; 
  }

  return sum_v/sum_w;
}

answered Mar 1 '12 at 17:39

Sai

1092 bronze badges

-1; I tested you code in the new Google Sheets and it didn't work, it gave #NUM! as a result. The code Lipis made still works. You're only ignoring the 2d-array that's being returned. – Jacob Jan Tuinstra Sep 11 '14 at 18:18

add a comment |

M.W. · Accepted Answer · 2013-08-07 00:39:14Z

ARRAYFORMULA() can calculate a weighted average in Google Spreadsheets (and more).

Storing the weights separately in B2:D2 simplifies reading the formula. Calculating the weighted average for E3 then looks similar to SUMPRODUCT() above (copy/paste for E4 & E5):

=ARRAYFORMULA(sum(B3:D3 * $B$2:$D$2)/sum($B$2:$D$2))

+--------+-------------+-------+-------------+---------+
|        | AttrA (50%) | AttrB | AttrC (300%)| Overall |
| Weight:|        50%  |  100% |        300% |         |
+--------+-------------+-------+-------------+---------+
| Prod 1 |       10    |  8    |       9     | 8.8889  |
| Prod 2 |        2    | 10    |       7     | 7.1111  |
| Prod 3 |        4    |  6    |       6     | 5.7778  |
+--------+-------------+-------+-------------+---------+

Using RegExExtract() to get the weights from $B$1:$D$1 is a simple change in the ARRAYFORMULA(). Replace the simple range with the more complex expression "iferror(regexextract($B$1:$D$1,"\d+"),100)/100" and the formula for E3 becomes (copy/paste for E4 & E5):

=ARRAYFORMULA(sum(B3:D3 * iferror(regexextract($B$1:$D$1,"\d+"),100)/100)/sum(iferror(regexextract($B$1:$D$1,"\d+"),100)/100))

N.B. That regexp requires attribute names without numbers; so, use AttrA, AttrB & AttrC instead of Attr1, Attr2 & Attr3.

Jacob Jan Tuinstra · Accepted Answer · 2014-09-11 19:57:58Z

Having read your question carefully,

Ideally, I'm looking for a solution that doesn't require creating temporary cells to help with the calculations.

I came up with this solution, that uses no temporary cells.

Formula

weights = ARRAYFORMULA(IFERROR(VALUE(REGEXEXTRACT($B$1:$D$1,"\((\d+)")),100))

=SUMPRODUCT(B3:D3, weights) / SUM(weights)

copy / paste
=SUMPRODUCT(B2:D2, ARRAYFORMULA(IFERROR(VALUE(REGEXEXTRACT($B$1:$D$1,"\((\d+)")),100)))/SUM(ARRAYFORMULA(IFERROR(VALUE(REGEXEXTRACT($B$1:$D$1,"\((\d+)")),100)))

Screenshot

enter image description here

Explained

The REGEXEXTRACT will extract the value in the header after the first straight bracket and VALUE will convert it into a number. The IFERROR will set the value at 100 if nothing is found and the ARRAYFORMULA will make it possible to select a range, rather than individual cells.

Example

I've created an example file for you: How to calculate weighted averages in Google Spreadsheets?

Community · Accepted Answer · 2017-04-13 12:57:20Z

Since the accepted answer is using Google Apps Script, I also created a little snippet.

Code

function weightedAverage(v, w) {
  var weights = [], sumWeights = 0;
  for(var k = 0, kLen = w[0].length; k < kLen; k++) {
    var m = w[0][k].match(/\((\d+)/), value;
    if(!m) {
      value = 100;
    } else {
      value = Number(m[1]);
    }
    weights.push(value);
    sumWeights += value;
  }

  var output = [];
  for(var i = 0, iLen = v.length; i < iLen; i++) {
    var sumProduct = 0;
    for(var j = 0, jLen = v[0].length; j < jLen; j++) {
      sumProduct += v[i][j] * weights[j]; 
    }
    output.push(sumProduct / sumWeights);
  } 
  return output;
}

Screenshot

enter image description here

Note

The script will follow the same logic as presented in this post. The main advantage is that it will calculate the overall values at once.

Example

I've created an example file for you: How to calculate weighted averages in Google Spreadsheets?

ale · Accepted Answer · 2016-09-19 23:26:09Z

For simple weighted averages, you can just add the cell value multiple times. Like if you want A1 to be 75% and B1 to be 25%, you can put in =AVERAGE (A1,A1,A1,B1). So specifically for yours, it would be =AVERAGE (B2,C2,C2,D1,D2,D2,D2,D2,D2,D2), which would weigh B2 as half of C2 (50%) and D2 as 3x C2 (300%).

More complicated stuff is above my pay grade. :)

ale · Accepted Answer · 2018-06-22 15:28:21Z

Actually both versions Average.weighted and sumproduct give the same result:

AVERAGE.WEIGHTED("Values","Weight")

SUMPRODUCT("Numbers","Weight")/sum("Weight")

So I rather use the first one since is way more simple to handle it, however I don't know how to add other weights to this formula.

This is the tooltip, but don't know how to add only weights and not additional values

AVERAGE.WEIGHTED(values, weights, [additional_values, ...], [additional_weights, ...])

This is a bit vague. Are you trying to ask a new question or is this an answer? — jonsca♦, Jun 24 '18 at 2:36

ale · Accepted Answer · 2014-09-11 22:44:29Z

-2

There is quite simple way using only given functions.

=(sumproduct(E5:E9;G5:G9)/sum(E5:E9))

where E contents is number of points and G the importance of those points.

edited Sep 11 '14 at 22:44

ale

43.6k25 gold badges119 silver badges263 bronze badges

answered Sep 11 '14 at 16:49

Adam

1

2

That answer was already given: webapps.stackexchange.com/a/29919/29140 – Jacob Jan Tuinstra Sep 11 '14 at 18:07
@JacobJanTuinstra Why does this answer show up first if it is rated way lower than the one you're referencing? webapps stackexchange works in misterious ways... – e18r Oct 16 '17 at 16:48

add a comment |

Stack Exchange Network

current community

your communities

more stack exchange communities

How to calculate weighted averages in Google Sheets?

11 Answers 11

Formula

Screenshot

Explained

Example

Code

Screenshot

Note

Example

Your Answer

Not the answer you're looking for? Browse other questions tagged google-sheets or ask your own question.

Linked

Hot Network Questions

How to calculate weighted averages in Google Sheets?

11 Answers 11

Formula

Screenshot

Explained

Example

Code

Screenshot

Note

Example

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged google-sheets or ask your own question.

Linked

Related

Hot Network Questions