Formula to help calculate % score based on different criteria

Rob0424

New Member
Joined
Jan 9, 2007
Messages
40
Office Version
  1. 2016
I am trying to figure out how to calculate a weighted % score based on criteria that is different. In columns G:T I have questions with Yes, No or N/A as selection options, in column F I have a 1-10 rating system. In row 1 I have entered %'s to weight the questions, totaling 100%. I'm trying to create a formula that will take all the "Yes" responses and calculate a % Score, which currently works:

=SUMPRODUCT((G5:T5 = "Yes") * $G$1:$T$1) / SUMIF(G5:T5, "<>N/A", $G$1:$T$1)

But now I want to add another criteria of using the rating system to factor into that % score as well. So If someone scores 8-10 it is good, 6-7 it is OK and 0-5 it is bad. Based on this it will adjust the % score being returned by the formula above. Any ideas?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
I am trying to figure out how to calculate a weighted % score based on criteria that is different. In columns G:T I have questions with Yes, No or N/A as selection options, in column F I have a 1-10 rating system. In row 1 I have entered %'s to weight the questions, totaling 100%. I'm trying to create a formula that will take all the "Yes" responses and calculate a % Score, which currently works:

=SUMPRODUCT((G5:T5 = "Yes") * $G$1:$T$1) / SUMIF(G5:T5, "<>N/A", $G$1:$T$1)

But now I want to add another criteria of using the rating system to factor into that % score as well. So If someone scores 8-10 it is good, 6-7 it is OK and 0-5 it is bad. Based on this it will adjust the % score being returned by the formula above. Any ideas?
Use xl2BB to share a sample of your data along with your desired output. Also, update your account details to which Excel version you're using to get a faster answer.
 
Upvote 0
100.00%10.00%5.00%10.00%10.00%5.00%10.00%2.50%10.00%2.50%10.00%2.50%5.00%10.00%2.50%5.00%
File StructureIX Prime ContractIX P&P BondsSubcontracts & Purchase OrdersPayment ApplicationsChange Orders
Job #% ScoreScoreOverall Rating (1-10) Organization of Project FileImproved from previous AuditUtilizes Standard File StructureFEC Saved in Proper LocationUtilizes Proper File NamingFEC Saved in Proper LocationUtilizes Proper File NamingFEC Saved in Proper LocationUtilizes Proper File NamingFinals Saved in Proper LocationUtilizes Proper File NamingMatches SpectrumFEC Saved in Proper LocationUtilizes Proper File NamingMatches Spectrum
18113558.82%F6N/ANoYesNoYesNoNoNoYesNoYesYesNoYes
 
Upvote 0
First time trying to upload as suggested but right now the formula does not take into account the #6 in the Overall Rating cell. I'm trying to figure out a way to incorporate that into the % Score calculation. I's trying to grade the structure of project folders based on the criteria entered.
 
Upvote 0
First time trying to upload as suggested but right now the formula does not take into account the #6 in the Overall Rating cell. I'm trying to figure out a way to incorporate that into the % Score calculation. I's trying to grade the structure of project folders based on the criteria entered.
Do you have in theory how you want to apply and you don't don't know how to carry it out OR you're looking for a theory to apply it?
 
Upvote 0
The theory I am currently using related to the cells that have Yes, No or N/A responses works, but I am trying to figure out how I can incorporate the rating system cell in that formula, so it becomes part of the grading system.
 
Upvote 0
The theory I am currently using related to the cells that have Yes, No or N/A responses works, but I am trying to figure out how I can incorporate the rating system cell in that formula, so it becomes part of the grading system.
You're asking a math question rather than an Excel question. There isn't one correct way to do this.

One comes to mind. Let the Overall Rating of OK have a factor of 1. (This is where your judgment call comes in). Then say if they're in the bad then it should be by 20% lower than the OK i.e. multiply by 0.80.

Similarly, the good rating is 20% better than the OK i.e. multiply by 1.2.

Alternatively, you can allocate some of the 100% weights of the questions to the overall rating. Again, how much allocation is your judgment call.
 
Upvote 0
Right, what you are talking about the formula I have already does, based on Yes, No or N/A. What it does not do is look at the cell with the 1-10 rating in it. How do I set something up to adjust the % score to factor in the 1-10 rating as well?
 
Upvote 0
First, determine how much % you want to allocate to the overall rating.
Let's say 10%.

Next, you need to make a judgment call. If it's good then give them all 10%. OK, 5% or bad 0%.
 
Upvote 0
Thanks but I'm looking for something that uses the 1-10 rating a bit more in the calculation. Appreciate your idea though. If I can't figure something out I will take that into consideration.
 
Upvote 0

Forum statistics

Threads
1,224,352
Messages
6,178,071
Members
452,822
Latest member
MtC

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