英文:
How to write one whole expression to get the column's weighted sum?
问题
Sure, here are the translated parts:
数据列包含四个值：1、1、2、3在Excel表中，每个数据都有它的权重，1的权重是0.9，2的权重是0.8，3的权重是0.7，我想要加权和。
- 在B2中写入表达式
=IF($A2=1,0.9,IF($A2=2,0.8,IF($A2=3,0.7)))。 - 从B2拖动填充手柄直到B5。
- 在B6中写入表达式
=SUM(B2:B5)。
我在B6中得到了正确的总和，我可以删除B列并在B1中写一个单一的表达式来获得加权总和吗？也就是说，将步骤1、步骤2和步骤3合并成一个单一的表达式？
英文:
The data column contains four value:1,1,2,3 in a excel table,every data has its weight, weight for 1 is 0.9,weight for 2 is 0.8,weight for 3 is 0.7,i want the weighted sum.
1.Write the expression =IF($A2=1,0.9,IF($A2=2,0.8,IF($A2=3,0.7))) in B2.
2.Drag the fill handle from B2 till B5.
3.Write the expression =sum(B2:B5) in B6.
I get the right sum in b6,can i delete B column and write a single expression in B1 to get weighted sum?That is to say,merge step1,step2,step3 into a one single expression?
答案1
得分: 1
使用SUMPRODUCT：
=SUMPRODUCT(1-(A2:A5*0.1))
如果您想要硬编码值，我建议使用INDEX/MATCH：
=SUMPRODUCT(INDEX({0.9,0.8,0.2},MATCH(A2:A5,{1,2,3},0)))
然后您可以更改数组为任何您喜欢的内容。
英文:
Use SUMPRODUCT:
=SUMPRODUCT(1-(A2:A5*0.1))
If you want to hard code the values then I suggest using INDEX/MATCH:
=SUMPRODUCT(INDEX({0.9,0.8,0.2},MATCH(A2:A5,{1,2,3},0)))
Then you can change the arrays to anything you like.
答案2
得分: 1
使用不同的SUMPRODUCT可以用于不同的权重：
=SUMPRODUCT(CHOOSE(A2:A5,0.9,0.8,0.2))
请注意，这是一个数组公式，需要使用<kbd>CTRL</kbd><kbd>SHIFT</kbd><kbd>ENTER</kbd>来确认。
英文:
A different SUMPRODUCT can be used for differing weights:
=SUMPRODUCT(CHOOSE(A2:A5,0.9,0.8,0.2))
Note that this is an array formula and will need to be confirmed with <kbd>CTRL</kbd><kbd>SHIFT</kbd><kbd>ENTER</kbd>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论