Calculating average of weighted grades in ever-expanding table

[tjdickinson]

I am creating a gradebook which has users add new evaluations through a VBA userform. Each new evaluation is added to the next empty column in the worksheet. Two of the data entered are the Category and the Points.

In the sheet Settings!K2:L17, the Category code (column K) is associated with a weight value (column L). For example, HW (homework) is weight 10, and TE (test) is weight 30.

I am not an expert in Excel, and I have been really struggling to come up with a formula for column B in the current (pictured) worksheet. It needs to do the following:

• convert the score in each column to a percent (ex. D$7/D$5)
• take the average of each array of percents based on the category (ex. AVERAGE(D$7/D$5, E$7/E$5) calculates the HW average, and AVERAGE(H$7/H$5, I$7/I$5, J$7/J$5, K$7/K$5) calculates the TE average), excluding blank cells
• multiply each average (which is, effectively, a percentage) by the weight value corresponding to the category in Settings!K2:L17
• sum the weighted results
• if the sum of the category weights used <> 100, then the sum of the weighted results is divided by the sum of the category weights used (ex. if we were only calculating results of the HW and TE categories, then the total of the weight values is 10+30=40, so the sum of the weighted results needs to be divided by 40 and multiplied by 100).
• expand the ranges when new columns are added

The other thing, tangentially related, is that the formula needs to fill down the column automatically. In the image above, the formula is in the first five rows of the table, but when I added a new student in the 6th row, the formula didn't fill down. I would like this to happen automatically.

Ideally, this would be a formula in cells B7:B.... (depending on how many students are in the class). However, if it is far too complicated for that, or if it would be significantly more efficient, I could add a button to the gradebook to 'update totals', and then use it to run a macro (but I've no idea how to write the macro, either).

I'm really grateful for any help on this. It's the last big step in the basic functionality of the gradebook. (There's still more to do, and more complicated things, I'm sure, but it's all essentially 'extra' compared to this very necessary feature.) Thanks again!

 

[Radoslaw Poprawski]

then i suggest highliting ur current data from row 7. andpress ctrl+t
this will convert range into a table
table does automatically fill in columns with formulas to the next added row.

 

[tjdickinson]

then i suggest highliting ur current data from row 7. andpress ctrl+t
this will convert range into a table
table does automatically fill in columns with formulas to the next added row.

Thanks, Radoslaw. I thought so, too. But it's already formatted as a table. I think the table merely formats the cells, it doesn't copy the formula. I have/had the same problem in row 6 with the class average; it needs to repeat each time a new evaluation is added (which, of course, is much more frequent than adding a new student, but still). In the example below, I had the table filled in through 'Sue' in A11, and the formula is in B7:B11. Then I added 'Tim' in A12, which extended the table, but didn't extend the formula.

Gradebook.xltm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Sint-Ignatius School 2A — Elementary English+ Mr T Dickinson 2021-2022		Evaluation Title	test 3	test 45	test 5	test 6	test 7	test 8	test9	test 10	next test	asdf	test
2			Category	HW	HW	WB	OP-GV	TE	TE	TE	TE	TE	TE	OP-GV
3			Assigned	04/05	12/05	25/12	24/12	15/12	12/12	12/12	12/12	12/12	12/12	01/12
4			Due	23/12	16/05	28/12	01/01	01/12	12/12	12/12	12/12	12/12	12/12	12/01
5			Points	14	25.1	16.1	18.1	20.0	20.0	17.1	12.1	140.0	100.0	50.0
6	Student Name	Total	Class Avg	9.7	13.4	10.8
7	John	15.7		14.0	25.0	8.0
8	Filip	8.7		7.0	3.0	16.0
9	Sam	7.8		9.3	2.0	12.0
10	Becky	12.7		6.0	25.0	7.0
11	Sue	11.7		12.0	12.0	11.0
12	Tim
13
14
15
16
Gradebook

Cell Formulas
Range	Formula
A1	A1	=TEXTJOIN(" ",TRUE,Settings!B1,Settings!B4&" — "&Settings!B3&" "&Settings!B2,Settings!B6,Settings!B5)
D6:M6	D6	=IFERROR(AVERAGE(D7:D11),"")
B7:B11	B7	=AVERAGE(Table7[@[Column4]:[Column6]])

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6:XFD1048576	Expression	=AND(D6/D$5<0.6,D6/D$5>=0.5)	text	NO
D6:XFD1048576	Expression	=AND(D6/D$5<0.5,D6/D$5>0)	text	NO

 

[Marcelo Branco]

The table is in the wrong rows.
Insert a row below row 6 and set the table as below (headers in row 7)
Drag the formula in D6 to the right (do not copy and paste)
Then insert a new student.
I think everything should work fine

05072021 Testes.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Sint-Ignatius School		Evaluation Title	test 3	test 45	test 5	test 6	test 7	test 8	test9	test 10	next test	asdf	test
2			Category	HW	HW	WB	OP-GV	TE	TE	TE	TE	TE	TE	OP-GV
3			Assigned	04/mai	12/mai	25/dez	24/dez	15/dez	12/dez	12/dez	12/dez	12/dez	12/dez	44531
4			Due	23/dez	16/mai	28/dez	01/jan	01/dez	12/dez	12/dez	12/dez	12/dez	12/dez	12
5			Points	14	25,1	16,1	18,1	20	20	17,1	12,1	140	100	50
6			Class Avg	9,7	13,4	10,8
7	Student Name	Total	xxxx	Result1	Result2	Result3	Result4	Result5	Result6	Result7	Result8	Result9	Result10	Result11
8	John	15,7		14,0	25,0	8,0
9	Filip	8,7		7,0	3,0	16,0
10	Sam	7,8		9,3	2,0	12,0
11	Becky	12,7		6,0	25,0	7,0
12	Sue	11,7		12,0	12,0	11,0
13
Plan7

Cell Formulas
Range	Formula
D6	D6	=IFERROR(AVERAGE(Table7[Result1]),"")
E6	E6	=IFERROR(AVERAGE(Table7[Result2]),"")
F6	F6	=IFERROR(AVERAGE(Table7[Result3]),"")
G6	G6	=IFERROR(AVERAGE(Table7[Result4]),"")
H6	H6	=IFERROR(AVERAGE(Table7[Result5]),"")
I6	I6	=IFERROR(AVERAGE(Table7[Result6]),"")
J6	J6	=IFERROR(AVERAGE(Table7[Result7]),"")
K6	K6	=IFERROR(AVERAGE(Table7[Result8]),"")
L6	L6	=IFERROR(AVERAGE(Table7[Result9]),"")
M6	M6	=IFERROR(AVERAGE(Table7[Result10]),"")
N6	N6	=IFERROR(AVERAGE(Table7[Result11]),"")
B8:B12	B8	=IFERROR(AVERAGE(Table7[@[Result1]:[Result11]]),"")

Hope this helps

M.