Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a Google Docs Spreadsheet 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?

share|improve this question
add comment

4 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
What do the $ signs mean? –  Senseful Dec 24 '10 at 23:41
    
@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
    
Thanks for that link and the answer. –  Senseful Dec 25 '10 at 17:38
add comment

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.

share|improve this answer
add comment

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)
share|improve this answer
    
+1 this answer is simple, working and much better than the accepted one. –  vertoe Sep 30 '13 at 8:56
add comment

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;
}​
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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