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?