Weighting Score

Y

yyasin

New Member
Joined
Dec 26, 2017
Messages
10
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})

[TABLE="width: 1035"]
<colgroup><col><col span="9"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Talent
Multiplier
(50%)[/TD]
[TD]Purpose
Driven
(6.25%)[/TD]
[TD]Principled
Agility
(6.25%)[/TD]
[TD]Results
Oriented
(6.25%)[/TD]
[TD]Strategic
Orientation
(6.25%)[/TD]
[TD]Innovation
(6.25%)[/TD]
[TD]Collaborative
Relationships
(6.25%)[/TD]
[TD]Risk
Mitigation
(6.25%)[/TD]
[TD]Change Champion
(6.25%)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
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



[TABLE="width: 1080"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
Is this a loss cause?
[TABLE="width: 1039"]
<tbody>[TR]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
 
Upvote 0
[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][td="bgcolor:#C0C0C0"]
F​
[/td][td="bgcolor:#C0C0C0"]
G​
[/td][td="bgcolor:#C0C0C0"]
H​
[/td][td="bgcolor:#C0C0C0"]
I​
[/td][td="bgcolor:#C0C0C0"]
J​
[/td][td="bgcolor:#C0C0C0"]
K​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td]Weight[/td][td]
50.00%​
[/td][td]
6.25%​
[/td][td]
6.25%​
[/td][td]
6.25%​
[/td][td]
6.25%​
[/td][td]
6.25%​
[/td][td]
6.25%​
[/td][td]
6.25%​
[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td][/td][td="bgcolor:#F3F3F3"]
Talent Multiplier​
[/td][td="bgcolor:#F3F3F3"]
Purpose Driven​
[/td][td="bgcolor:#F3F3F3"]
Principled Agility​
[/td][td="bgcolor:#F3F3F3"]
Results Oriented​
[/td][td="bgcolor:#F3F3F3"]
Strategic Orientation​
[/td][td="bgcolor:#F3F3F3"]
Innovation​
[/td][td="bgcolor:#F3F3F3"]
Collaborative Relationships​
[/td][td="bgcolor:#F3F3F3"]
Risk Mitigation​
[/td][td="bgcolor:#F3F3F3"]
Overall​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td]Alan[/td][td]
87​
[/td][td]
88​
[/td][td]
78​
[/td][td]
87​
[/td][td]
82​
[/td][td]
95​
[/td][td]
85​
[/td][td]
92​
[/td][td="bgcolor:#E5E5E5"]
81.44​
[/td][td]J3: =SUMPRODUCT($B$1:$I$1, B3:I3)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]Barb[/td][td]
95​
[/td][td]
76​
[/td][td]
83​
[/td][td]
89​
[/td][td]
82​
[/td][td]
91​
[/td][td]
97​
[/td][td]
98​
[/td][td="bgcolor:#E5E5E5"]
86.00​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]Cain[/td][td]
82​
[/td][td]
85​
[/td][td]
98​
[/td][td]
92​
[/td][td]
78​
[/td][td]
88​
[/td][td]
81​
[/td][td]
90​
[/td][td="bgcolor:#E5E5E5"]
79.25​
[/td][td][/td][/tr]
[/table]
 
Upvote 0
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.
 
Upvote 0
[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][td="bgcolor:#C0C0C0"]
F​
[/td][td="bgcolor:#C0C0C0"]
G​
[/td][td="bgcolor:#C0C0C0"]
H​
[/td][td="bgcolor:#C0C0C0"]
I​
[/td][td="bgcolor:#C0C0C0"]
J​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td]Weight[/td][td]
50.00%​
[/td][td]
7.14%​
[/td][td]
7.14%​
[/td][td]
7.14%​
[/td][td]
7.14%​
[/td][td]
7.14%​
[/td][td]
7.14%​
[/td][td]
7.14%​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td][/td][td="bgcolor:#F3F3F3"]
Talent Multiplier​
[/td][td="bgcolor:#F3F3F3"]
Purpose Driven​
[/td][td="bgcolor:#F3F3F3"]
Principled Agility​
[/td][td="bgcolor:#F3F3F3"]
Results Oriented​
[/td][td="bgcolor:#F3F3F3"]
Strategic Orientation​
[/td][td="bgcolor:#F3F3F3"]
Innovation​
[/td][td="bgcolor:#F3F3F3"]
Collaborative Relationships​
[/td][td="bgcolor:#F3F3F3"]
Risk Mitigation​
[/td][td="bgcolor:#F3F3F3"]
Overall​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td]Alan[/td][td]Expert[/td][td]Expert[/td][td]Expert[/td][td]Expert[/td][td]Expert[/td][td]Expert[/td][td]Expert[/td][td]Expert[/td][td="bgcolor:#E5E5E5"]
5.00​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]Barb[/td][td]Advanced[/td][td]Advanced[/td][td]Advanced[/td][td]Advanced[/td][td]Advanced[/td][td]Basic[/td][td]Expert[/td][td]Expert[/td][td="bgcolor:#E5E5E5"]
4.07​
[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]Cain[/td][td]Needs Development[/td][td]Needs Development[/td][td]Needs Development[/td][td]Needs Development[/td][td]Needs Development[/td][td]Needs Development[/td][td]Needs Development[/td][td]Needs Development[/td][td="bgcolor:#E5E5E5"]
1.00​
[/td][/tr]
[/table]

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%.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Weighted scores
Replies
6
Views
878
FryGirl
F
L
  • Solved
If formula with values between x and y
Replies
9
Views
551
smozgur
smozgur
Y
  • Question Question
Sum based on Text
Replies
2
Views
634
yyasin
Y
G
  • Question Question
MATCHing an ARRAY within a SUMPRODUCT
Replies
3
Views
959
pgc01
P
L
  • Question Question
Sub-Weighted Scorecard
Replies
0
Views
2K
lorr91
L

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top