Weighted Score not counting blanks

[LotsofQuestions1]

Hi,

I was using a format for a scoring tool to show a scoring of 1,0 and blank for items that weren’t applicable. C6-C36 could have a total of 31. The formula I was using was =Sum(c6:c36)/count(c6:c36) with a score that would essentially equal 100%.

However, I’ve been asked to weight some areas differently. Column C6-C36 can now have a total score of 43 but C13 and C22 can be a possible score of 6, C15 can possibly score 3. I’m trying to figure out what the best formula would be to figure out the possible weighted scores to equal 100%. I have an older excel version:2016.

Thank you

 

[Gerald Higgins]

Hi, it's not clear (to me at least) what exactly you want.
Can you post an example of your data, and say what the results should be, and why ?

 

[LotsofQuestions1]

I have to type it out bc it won’t let me upload a picture:

Current scoring:
Scoring Column c- scoring begins in column C5:
C5 score 0-if they didn’t meet the criteria, 1 if they did meet the criteria, blank- if it doesn’t apply
C6 score 0-if they didn’t meet the criteria, 1 if they did meet the criteria, blank- if it doesn’t apply
C7 score 0-if they didn’t meet the criteria, 1 if they did meet the criteria, blank- if it doesn’t apply
Etc. through column 36

The total score (with the formula listed above is in column c37).

Now I am being asked to change it and have 3 of the columns equal a different weight so:

Scoring Column c-Scoring begins in Column C5:
C5 score 0-if they didn’t meet the criteria, 1 if they did meet the criteria, blank- if it doesn’t apply
C6 score 0-if they didn’t meet the criteria, 1 if they did meet the criteria, blank- if it doesn’t apply
C7 score 0-if they didn’t meet the criteria, 1 if they did meet the criteria, blank- if it doesn’t apply
Etc on all columns except for:
C13 (total possible score of 6) , C22 (total possible score of 6) and C15 (total possible score of 2.

I’m being asked to change 3 different column scores but my old formula doesn’t work with the new weighted scores.

 

[Gerald Higgins]

I don't really understand what you want to do.

What does this mean exactly ?

C13 (total possible score of 6) , C22 (total possible score of 6) and C15 (total possible score of 2.

What do you want the results to be ?

 

[LotsofQuestions1]

Those are the new weighted areas so instead of C22 scoring a max of 1, it now has the possibility to score up to 6. I want the total possible score to be added up and dividing by the total possible amount, understanding that 3 areas can now have higher weights. I also do not want areas left blank to count for or against a acore

 

[Gerald Higgins]

Still not clear to me.
Before we work out how to do this in Excel, you yourself need to be clear about what the results should be.

Let's try and simplify this a little.

Let's say you only had two questions to deal with -

C7 score 0-if they didn’t meet the criteria, 1 if they did meet the criteria, blank- if it doesn’t apply

and

C13 (total possible score of 6)

Please give the required results for EACH and ALL of these situations -

C7 answer is 0, C13 answer is 0
C7 answer is 0, C13 answer is 3
C7 answer is 0, C13 answer is 6
C7 answer is 1, C13 answer is 0
C7 answer is 1, C13 answer is 3
C7 answer is 1, C13 answer is 6
C7 answer is blank, C13 answer is 0
C7 answer is blank, C13 answer is 3
C7 answer is blank, C13 answer is 6

 

[LotsofQuestions1]

Still not clear to me.
Before we work out how to do this in Excel, you yourself need to be clear about what the results should be.

Let's try and simplify this a little.

Let's say you only had two questions to deal with -

and


Please give the required results for EACH and ALL of these situations -

C7 answer is 0, C13 answer is 0
C7 answer is 0, C13 answer is 3
C7 answer is 0, C13 answer is 6
C7 answer is 1, C13 answer is 0
C7 answer is 1, C13 answer is 3
C7 answer is 1, C13 answer is 6
C7 answer is blank, C13 answer is 0
C7 answer is blank, C13 answer is 3
C7 answer is blank, C13 answer is 6

I think this might help, I was able to copy the spreadsheet.
Previously when all the scores were weighted at 0,1 or blank, I used this formula where the percentage is at the bottom (currently showing as 268%): =Sum(c6:c36)/count(c6:c36). Everything always totaled to 100% but now that 3 areas have a max weight of 5, my formula needs to be corrected. I am obviously not formally trained and have learned most of it by searches or being shown by a colleague, so I apologize if my ask isn’t very clear.

	Criteria	Scores Met: See weighted score Unmet: 0 N/A: Leave Blank
Weighted Score- Max potential points a person could receive, per question
1		1
1		1
1		1
1		1
1		1
1		1
1		1
5		5
1		1
1		1
5		5
1		1
1		1
1		1
1		1
1		1
5		1
1		1
1		1
1		1
1		1
1		1
1		1
1		1
1		1
1		1
1		1
1		1
1		1
1		1
1		1
		268%	I want this score to equal 100% if they get the max weighted scoring.
			If they score a 0, their score should decrease and if they score less than the max weight of 5 (if applicable), their score should also decrease
			If they have a blank section, their score should not decrease or increase and it won't be affected b/c that section may not apply

 

[Gerald Higgins]

Yeah, posting a bunch of "1" characters doesn't help me that much

In your last post, you have three lines where the maximum possible score is 5.

So, what exactly should the total score be if the score for ONE of these is 3 ?

 

[LotsofQuestions1]

Yeah, posting a bunch of "1" characters doesn't help me that much

In your last post, you have three lines where the maximum possible score is 5.

So, what exactly should the total score be if the score for ONE of these is 3 ?

If the score for one of these is 3, the total score should be 5. 3 areas have a max weighted score of 5, the other areas have a max weighted score of 1

 

[Gerald Higgins]

How can it be 5 ?
I thought you wanted it to be 100%, or less than 100% if some answers are less than the max possible.