Formula to help calculate % score based on different criteria

[Rob0424]

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?

 

[BigBeachBananas]

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.

 

[Rob0424]

			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 Structure	IX Prime Contract		IX P&P Bonds		Subcontracts & Purchase Orders		Payment Applications			Change Orders
Job #		% Score	Score	Overall Rating (1-10) Organization of Project File	Improved from previous Audit	Utilizes Standard File Structure	FEC Saved in Proper Location	Utilizes Proper File Naming	FEC Saved in Proper Location	Utilizes Proper File Naming	FEC Saved in Proper Location	Utilizes Proper File Naming	Finals Saved in Proper Location	Utilizes Proper File Naming	Matches Spectrum	FEC Saved in Proper Location	Utilizes Proper File Naming	Matches Spectrum
181135		58.82%	F	6	N/A	No	Yes	No	Yes	No	No	No	Yes	No	Yes	Yes	No	Yes

 

[Rob0424]

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.

 

[BigBeachBananas]

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?

 

[Rob0424]

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.

 

[BigBeachBananas]

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.

 

[Rob0424]

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?

 

[BigBeachBananas]

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%.

 

[Rob0424]

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.