Weighting Score

[yyasin]

Hello,

I'm hoping someone can help me with this.

I have a total score being calculated based on 5 options under 9 categories (below is the formula). I need to make add a 50% weighting to the first category and then evenly distribute to the remaining 8. Does anyone have the slightest idea how this can be accomplished?

=SUM(COUNTIF(I3:Q3,{"Needs Development","Basic","Intermediate","Advanced","Expert"})*{1,2,3,4,5})

	Talent Multiplier (50%)	Purpose Driven (6.25%)	Principled Agility (6.25%)	Results Oriented (6.25%)	Strategic Orientation (6.25%)	Innovation (6.25%)	Collaborative Relationships (6.25%)	Risk Mitigation (6.25%)	Change Champion (6.25%)

<colgroup><col><col span="9"></colgroup><tbody>
</tbody>

 

[cunningAce]

How are you posting the scores?
Using text, Basic, Expert etc.? or their equivalent score value, 2,5?

If you used a number as the score method for each 9 categories you could use the following formula to give you a total score based on the weights you want.

=SUMPRODUCT(A4:I4,A2:I2)/SUM(A2:I2)

Assuming the scores are in A4:I4 and the % Weight in A2:I2

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[yyasin]

The scores are automatically totaled based what is chosen for each person in the 9 categories (Talent Multiplier, Purpose Driven etc.). I need to add weight to each of those 9 categories. I need the formula to account for the weight of the choice (Basic, Expert etc.) and the weight of the category.

 

[yyasin]

Is this a loss cause?

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col></colgroup>

 

[shg]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​	Weight	50.00%​	6.25%​	6.25%​	6.25%​	6.25%​	6.25%​	6.25%​	6.25%​
2​		Talent Multiplier​	Purpose Driven​	Principled Agility​	Results Oriented​	Strategic Orientation​	Innovation​	Collaborative Relationships​	Risk Mitigation​	Overall​
3​	Alan	87​	88​	78​	87​	82​	95​	85​	92​	81.44​	J3: =SUMPRODUCT($B$1:$I$1, B3:I3)
4​	Barb	95​	76​	83​	89​	82​	91​	97​	98​	86.00​
5​	Cain	82​	85​	98​	92​	78​	88​	81​	90​	79.25​

 

[yyasin]

Thank you shg, but what if I have text in B3:I3? The file is setup so that Needs Improvement, Basic, Intermediate, Advanced, and Expert are displayed in a dropdown.

 

[shg]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Weight	50.00%​	7.14%​	7.14%​	7.14%​	7.14%​	7.14%​	7.14%​	7.14%​
2​		Talent Multiplier​	Purpose Driven​	Principled Agility​	Results Oriented​	Strategic Orientation​	Innovation​	Collaborative Relationships​	Risk Mitigation​	Overall​
3​	Alan	Expert	Expert	Expert	Expert	Expert	Expert	Expert	Expert	5.00​
4​	Barb	Advanced	Advanced	Advanced	Advanced	Advanced	Basic	Expert	Expert	4.07​
5​	Cain	Needs Development	Needs Development	Needs Development	Needs Development	Needs Development	Needs Development	Needs Development	Needs Development	1.00​

The formula in J3 and copied down is

=SUMPRODUCT($B$1:$I$1, LOOKUP(B3:I3, {"Advanced","Basic","Expert","Intermediate","Needs Development"}, {4,3,5,2,1}))

I changed the order of the ratings so they are in alpha order, which LOOKUP requires. I changed the weights so they total 100%.

 

[yyasin]

That's it! Thank you very much shg!

 

[shg]

You're welcome.