Redistributing weighted score total if multiple answers not given

I am creating a scoring system for a contest and need to redistribute the weight of one or more questions if they do not get scored. For example:

Q1 weight 25%
Q2 weight 5%
Q3 weight 5%
Q4 weight 15%
Q5 weight 25%
Q6 weight 10%
Q7 weight 15%
Total 100% possible score

Judge would score for each question 1-10

Example score sheet

Q1 - 7
Q2 - not scored
Q3 - 9
Q4 - not scored
Q5 - 6
Q6- 10
Q7 -7

In this case Q2 and 4 did not get scored so 20% would get divided into the other 5 scores. (4% into each of the other 5 scores)

How is a formula created for this so that whenever one or more scores are not answered the weight gets redistributed into the others.

Thank you.

Bebo, thank you. That works perfectly. You have saved me so much work.

Originally Posted by bebo021999

=IF(C2="","",(B2+SUMIF($C$2:$C$8,"",$B$2:$B$8)/COUNTA($C$2:$C$8))*C2)

I do not believe that is correct. SUM(D2:D8) should be the weighted average. But that sum is 7.31.

I believe the correct weighted average is 7.1875, as demonstrated below. (Also see the attached file.)

	A	B	C	D	E	F
1		Weight	Score	Distribute	Correct
2	Q1	25%	7	2.03	2.1875
3	Q2	5%
4	Q3	5%	9	0.81	0.5625
5	Q4	15%
6	Q5	25%	6	1.74	1.875
7	Q6	10%	10	1.4	1.25
8	Q7	15%	7	1.33	1.3125
9				7.31	7.1875	total
10					7.1875	wgtd avg

Please Login or Register  to view this content.

Joeu2004, Thank you. Yes I see the slight error in the other formula. Thank you for correcting it.