# Calculating average of weighted grades in ever-expanding table

#### tjdickinson

##### Board Regular
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!

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

##### Active Member
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

##### Board Regular
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.

ABCDEFGHIJKLMNOPQ
1Sint-Ignatius School 2A — Elementary English+ Mr T Dickinson 2021-2022Evaluation Titletest 3test 45test 5test 6test 7test 8test9test 10next testasdftest
2CategoryHWHWWBOP-GVTETETETETETEOP-GV
3Assigned04/0512/0525/1224/1215/1212/1212/1212/1212/1212/1201/12
4Due23/1216/0528/1201/0101/1212/1212/1212/1212/1212/1212/01
5Points1425.116.118.120.020.017.112.1140.0100.050.0
6Student NameTotalClass Avg9.713.410.8
7John15.714.025.08.0
8Filip8.77.03.016.0
9Sam7.89.32.012.0
10Becky12.76.025.07.0
11Sue11.712.012.011.0
12Tim
13
14
15
16
Cell Formulas
RangeFormula
A1A1=TEXTJOIN(" ",TRUE,Settings!B1,Settings!B4&" — "&Settings!B3&" "&Settings!B2,Settings!B6,Settings!B5)
D6:M6D6=IFERROR(AVERAGE(D7:D11),"")
B7:B11B7=AVERAGE(Table7[@[Column4]:[Column6]])
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D6:XFD1048576Expression=AND(D6/D\$5<0.6,D6/D\$5>=0.5)textNO
D6:XFD1048576Expression=AND(D6/D\$5<0.5,D6/D\$5>0)textNO

#### Marcelo Branco

##### MrExcel MVP
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
ABCDEFGHIJKLMN
1Sint-Ignatius SchoolEvaluation Titletest 3test 45test 5test 6test 7test 8test9test 10next testasdftest
2CategoryHWHWWBOP-GVTETETETETETEOP-GV
3Assigned04/mai12/mai25/dez24/dez15/dez12/dez12/dez12/dez12/dez12/dez44531
4Due23/dez16/mai28/dez01/jan01/dez12/dez12/dez12/dez12/dez12/dez12
5Points1425,116,118,1202017,112,114010050
6Class Avg9,713,410,8
7Student NameTotalxxxxResult1Result2Result3Result4Result5Result6Result7Result8Result9Result10Result11
8John15,714,025,08,0
9Filip8,77,03,016,0
10Sam7,89,32,012,0
11Becky12,76,025,07,0
12Sue11,712,012,011,0
13
Plan7
Cell Formulas
RangeFormula
D6D6=IFERROR(AVERAGE(Table7[Result1]),"")
E6E6=IFERROR(AVERAGE(Table7[Result2]),"")
F6F6=IFERROR(AVERAGE(Table7[Result3]),"")
G6G6=IFERROR(AVERAGE(Table7[Result4]),"")
H6H6=IFERROR(AVERAGE(Table7[Result5]),"")
I6I6=IFERROR(AVERAGE(Table7[Result6]),"")
J6J6=IFERROR(AVERAGE(Table7[Result7]),"")
K6K6=IFERROR(AVERAGE(Table7[Result8]),"")
L6L6=IFERROR(AVERAGE(Table7[Result9]),"")
M6M6=IFERROR(AVERAGE(Table7[Result10]),"")
N6N6=IFERROR(AVERAGE(Table7[Result11]),"")
B8:B12B8=IFERROR(AVERAGE(Table7[@[Result1]:[Result11]]),"")

Hope this helps

M.

Replies
3
Views
113
Replies
0
Views
291
Replies
3
Views
470
Replies
7
Views
665
Replies
15
Views
476

1,186,112
Messages
5,955,912
Members
438,225
Latest member
rsur

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back