How to Create a Weighted Scoring Model
In this video, I'll guide you through multiple methods to create a weighted scoring model in Excel. You'll learn about choosing the best location, designing a weighted scoring model to rank the employees, determining the highest priority, and finding weighted average. A weighted scoring model can be used to analyze and rank multiple criteria, and to evaluate potential projects, suppliers, or job candidates. With practical examples and step-by-step instructions, you can effortlessly make a weighted scoring model in your own Excel spreadsheets.
👨🏫 Instructor: Zehad Rian Jim
🎥 Editor: Md. Riajul Islam
✨ ⯆ Resources:
ALT= - To apply the SUM function
▬ Contents of this video ▬▬▬▬▬▬▬▬▬▬
0:00 - Intro
1:10 - Choosing the best location
3:14 - Designing a weighted scoring model to rank the employees
6:37 - Determining the highest priority
8:26 - Finding weighted average
📚 ⯆ DOWNLOAD the workbook here:
www.exceldemy.com/create-a-we...
🌍 ⯆ Checkout the article here:
www.exceldemy.com/create-a-we...
🚩 Stay connected with us on social media for more Excel tips and tricks!
Facebook: / exceldemy
Twitter: / exceldemy
LinkedIn: / exceldemy
🙋♂️ Stuck with an Excel formula or a VBA code? You can post your questions or upload your Excel file to get in touch with the professionals and get the solution you need.
ExcelDemy Forum: exceldemy.com/forum/
👉 If you found this video helpful, don't forget to subscribe to our channel for more Excel tutorials, tips, and tricks! Hit the subscribe button and turn on notifications, so you never miss an upload. By subscribing, you'll be supporting our channel and helping us to reach more people who can benefit from our content. Thank you for watching, and we'll see you in the next video!
🔔 ⯆ Subscribe on KZread:
/ @exceldemy2006
#excel #exceltutorial #exceltips #exceltrick
Пікірлер: 15
Underrated Tutorial
@exceldemy2006
4 ай бұрын
Dear @Flamebox2000 , To grow more please stay with us. Your appreciation means a lot to us. Regards ExcelDemy
Excellent tutorial!
VERY GOOD TUTORIAL.
@exceldemy2006
4 ай бұрын
Glad you think so!
Really great tutorial. I noticed in all examples you have used Sumproduct divided by Sum to get the weighted average, except for the example with employee rank where u only used Sumproduct. Why is that?
@exceldemy2006
2 ай бұрын
Dear, Thanks for your question! We use the SUMPRODUCT divided by SUM in the examples to calculate the weighted average. However, when it comes to employee ranking, we only use the SUMPRODUCT. This is because we are calculating each employee's total weighted score. As we are not trying to find an average, dividing by the sum of weights is unnecessary.
Magnificent
@exceldemy2006
Ай бұрын
Hello @ronx761, You are most welcome. Please stay connected with us. Regards ExcelDemy
Great video! With the 'rank' example... how have you determined the weight values used this method? Should these total a specific amount or percentage?
@exceldemy2006
3 ай бұрын
Hello @tahneebaker376, Thanks for your appreciation. The weight values are not fixed these are given based on the importance of the criteria. The total should be 100. When determining weight values while ranking employees, the specific criteria and the importance of each depend on organizational goals and role requirements. Generally, the criteria are performance metrics, skills proficiency, teamwork, leadership, innovation, etc.
How do you apply a Score between 1-5 (1 being the best, 5 being the worst) based upon each row's weighted value of the total?
@exceldemy2006
Ай бұрын
Dear, Thanks for your questions! YES, you can easily apply a score between 1 and 5 (1 being the best, 5 being the worst) within the existing model mentioned in the first example. To do so, you do not need to change the formula. However, remember to ensure the sum of the weights equals 100% or 1 in decimal representation. We have demonstrated your situation in an Excel file. You can download the file: www.exceldemy.com/wp-content/uploads/2024/06/Joe-LaFond-SOLVED.xlsx
@justsayjoe9634
Ай бұрын
@@exceldemy2006 Thank you for your response to my question. I took a look at your solution, and it was close, but no cigar. I'm not trying to determine what the weighted average of the scores are, I'm trying to apply a score (between 1-5) to each row, based on the row's weight value. So the row with the highest percentage share will get a score of 1 (the best), the row with the lowest percentage share will get a score of 5 (the worst), and the rows between those two are where I'm having problems figuring out how to apply a score to. I hope that makes more sense.
@exceldemy2006
Ай бұрын
@@justsayjoe9634 Dear, Thanks for your kind words! You want to assign scores (between 1 and 5) to each row based on its weight value. Please check the following: www.exceldemy.com/wp-content/uploads/2024/06/Assigning-scores-between-1-and-5-to-each-row-based-on-its-weight-value.gif You can download the workbook: www.exceldemy.com/wp-content/uploads/2024/06/Joe-LaFond-SOLVED-1.xlsx Improved Excel Formula: =COUNTA($C$5:$C$9)-RANK.EQ(C5, $C$5:$C$9, 1)