Excel-如何根据两个字段计算分数 [英] Excel - how to calculate a score based on two fields
问题描述
我试图弄清楚如何计算基于两个不同字段的名为风险分数"的字段,而无需使用嵌套的if循环.
I'm trying to figure out how to calculate a field called "Risk Score" that is based on two different fields without using nested if loops.
这两个字段是风险可能性"和风险影响",其中可能性将以0-100%之间的百分比形式输入,而影响将以高,中,低形式输入.
The two fields are "Likelihood of Risk" and "Impact of Risk" where the likelihood will be inputted as a % between 0-100% and the impact inputted as either high, medium, low.
下面是两个字段和计算字段的示例:
Below is an example of the two fields and the calculated fields:
以下是我想在基于两个字段的第三个字段中得出的得分.
Below is the scoring I would like to derive in the third field based on the two fields.
我不认为我可以使用索引匹配匹配",因为可能性得分基于范围而不是特定值.
I don't think I could use Index Match Match since the likelihood score is based on a range and not a specific value.
如果您对如何创建风险评分公式有任何想法,请告诉我.谢谢!
Please let me know if you have ideas on how I should create the formula for Risk Score. Thanks!
推荐答案
将%s的范围更改为最小值:
Change the ranges of %s to the minimums:
然后是简单的INDEX/MATCH/MATCH:
Then a simple INDEX/MATCH/MATCH:
=INDEX($C$3:$E$5,MATCH(H3,$B$3:$B$5,1),MATCH(G3,$C$2:$E$2))
这篇关于Excel-如何根据两个字段计算分数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!