Grade sheet which discards empty cells

[MaddogJason]

A small grade spreadsheet challenge to you rockstars.
This seems like an easy one, I thought I would be able to solve it, but....
Row 2 is how much each task weighs, in row 1 you see the percentage.
John has done three tasks columns B, C, D, He gets the grade in column E based on the average that takes the weight of the task in mind. Works perfectly.
Lisa has not done the task in Column C and I need a function that discards that fact, so she should get grade 5. So when any task is missing it will not take it into account in the calculation.
As a bonus, it would be great to have a function that could easily be adjusted if a new column (task) would be added.

CocoaBreak3.0.xlsm
	A	B	C	D	E
1	%	25	25	50
2	Weight	1	1	2
3		Task 1	Homework	Test
4	John	6	4	5	5.00
5	Lisa	5		5	3.75
week 3

Cell Formulas
Range	Formula
B1:D1	B1	=(100*B2)/($B$2+$C$2+$D$2)
E4:E5	E4	=((B4*$B$2)+(C4*$C$2)+(D4*$D$2))/(SUM($B$2:$D$2))

 

[Fluff]

If a cell is blank would it always be treated as a 5?

 

[jtakw]

Hi,

You can change the SUM to SUMIF:

Book3.xlsx
	A	B	C	D	E
1	%	25	25	50
2	Weight	1	1	2
3		Task 1	Homework	Test
4	John	6	4	5	5
5	Lisa	5		5	5
Sheet836

Cell Formulas
Range	Formula
B1:D1	B1	=B2/SUM($B2:$D2)*100
E4:E5	E4	=(B4*B$2+C4*C$2+D4*D$2)/SUMIF(B4:D4,"<>",B$2:D$2)

 

[kweaver]

One thought -- is this correct?

=SUM($B$2:$D$2*B4:D4)/(SUM($B$2:$D$2*ISNUMBER(B4:D4)))

Book1 (version 1).xlsb
	A	B	C	D	E
1	%	25	25	50
2	Weight	1	1	2
3		Task 1	HW	Test
4	John	6	4	5	5
5	Lisa	5		5	5
6	Jason		10	20	16.66667
Sheet6

Cell Formulas
Range	Formula
E4:E6	E4	=SUM($B$2:$D$2*B4:D4)/(SUM($B$2:$D$2*ISNUMBER(B4:D4)))

 

[jtakw]

As a bonus, it would be great to have a function that could easily be adjusted if a new column (task) would be added.

Adding a SUMPRODUCT option:

Book3.xlsx
	A	B	C	D	E	F
1	%	25	25	50
2	Weight	1	1	2
3		Task 1	Homework	Test
4	John	6	4	5	5	5
5	Lisa	5		5	5	5
Sheet836

Cell Formulas
Range	Formula
B1:D1	B1	=B2/SUM($B2:$D2)*100
E4:E5	E4	=(B4*B$2+C4*C$2+D4*D$2)/SUMIF(B4:D4,"<>",B$2:D$2)
F4:F5	F4	=SUMPRODUCT(B$2:D$2*B4:D4)/SUMIF(B4:D4,"<>",B$2:D$2)

 

[Fluff]

A couple of other possibilities.

+Fluff 1.xlsm
	A	B	C	D	E	F
1	%	25	25	50
2	Weight	1	1	2
3		Task 1	Homework	Test
4	John	6	4	5	5	5
5	Lisa	5		5	5	5
6	Pete	6		5	5.25	5.375
Master

Cell Formulas
Range	Formula
B1:D1	B1	=(100*B2)/($B$2+$C$2+$D$2)
E4:E6	E4	=SUMPRODUCT(IF(B4:D4="",5,B4:D4),B$2:D$2)/SUM(B$2:D$2)
F4:F6	F4	=SUMPRODUCT(IF(B4:D4="",AVERAGE(B4:D4),B4:D4),B$2:D$2)/SUM(B$2:D$2)

 

[MaddogJason]

THANK YOU ALL FOR YOUR HELP!!!!
It always amazes me how quickly I get answers in this forum. Makes me wonder why I even bother to try it myself... When I see the functions I get them, but figuring them out on my own still seems next to impossible.
Below are the functions that worked.
Fluff's functions seem to get confused with the average and does not take into account which specific tasks has what specific weight.
Now the question is which one works the best when adding a column? Perhaps I need to record a macro to include the new column. The other option would be to put the function in column C and then just widen the range by a lot and then basically be able to just add tasks if need be.
Thank you once again for all the help and good solutions. I will obviously take all the credit for this success and brag about my mad Excel skills!

CocoaBreak3.0.xlsm
	A	B	C	D	E
1	%	29	29	43
2	Weight	2	2	3
3		Task 1	Homework	Test
4	Fluff 1	4		6	5.14
5	Fluff 2	4		6	5.14
6	Jtakw	4	4.67	6	5.05
7	Paul	4		6	5.20
8	Kwever	4		6	5.20
9	Jtakw	4		6	5.20
10	Maddog	4		6	5.20
week 3

Cell Formulas
Range	Formula
B1:D1	B1	=B2/SUM($B2:$D2)*100
E4	E4	=SUMPRODUCT(IF(B4:D4="",AVERAGE(B4:D4),B4:D4),B$2:D$2)/SUM(B$2:D$2)
E5	E5	=SUMPRODUCT(IF(B5:D5="",5,B5:D5),B$2:D$2)/SUM(B$2:D$2)
E6	E6	=SUMPRODUCT(B$2:D$2*B6:D6)/SUMIF(B6:D6,"<>",B$2:D$2)
E7,E9	E7	=(B7*B$2+C7*C$2+D7*D$2)/SUMIF(B7:D7,"<>",B$2:D$2)
E8	E8	=SUM($B$2:$D$2*B8:D8)/(SUM($B$2:$D$2*ISNUMBER(B8:D8)))
E10	E10	=((B10*$B$2)+(D10*$D$2))/(SUM($B$2+$D$2))

 

[MaddogJason]

THANK YOU ALL FOR YOUR HELP!!!!
It always amazes me how quickly I get answers in this forum. Makes me wonder why I even bother to try it myself... When I see the functions I get them, but figuring them out on my own still seems next to impossible.
Below are the functions that worked.
Fluff's functions seem to get confused with the average and does not take into account which specific tasks has what specific weight.
Now the question is which one works the best when adding a column? Perhaps I need to record a macro to include the new column. The other option would be to put the function in column C and then just widen the range by a lot and then basically be able to just add tasks if need be.
Thank you once again for all the help and good solutions. I will obviously take all the credit for this success and brag about my mad Excel skills!

CocoaBreak3.0.xlsm
	A	B	C	D	E
1	%	29	29	43
2	Weight	2	2	3
3		Task 1	Homework	Test
4	Fluff 1	4		6	5.14
5	Fluff 2	4		6	5.14
6	Jtakw	4	4.67	6	5.05
7	Paul	4		6	5.20
8	Kwever	4		6	5.20
9	Jtakw	4		6	5.20
10	Maddog	4		6	5.20
week 3

Cell Formulas
Range	Formula
B1:D1	B1	=B2/SUM($B2:$D2)*100
E4	E4	=SUMPRODUCT(IF(B4:D4="",AVERAGE(B4:D4),B4:D4),B$2:D$2)/SUM(B$2:D$2)
E5	E5	=SUMPRODUCT(IF(B5:D5="",5,B5:D5),B$2:D$2)/SUM(B$2:D$2)
E6	E6	=SUMPRODUCT(B$2:D$2*B6:D6)/SUMIF(B6:D6,"<>",B$2:D$2)
E7,E9	E7	=(B7*B$2+C7*C$2+D7*D$2)/SUMIF(B7:D7,"<>",B$2:D$2)
E8	E8	=SUM($B$2:$D$2*B8:D8)/(SUM($B$2:$D$2*ISNUMBER(B8:D8)))
E10	E10	=((B10*$B$2)+(D10*$D$2))/(SUM($B$2+$D$2))

 

[MaddogJason]

The data in C6 is incorrect from my side, the function works.

 

[MaddogJason]

If a cell is blank would it always be treated as a 5?

No, when the cell is blank it should be completely discarded from the calculation.