Calculating average of weighted grades in ever-expanding table

tjdickinson

New Member
Joined
Jun 26, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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.

1624740345642.png


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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Radoslaw Poprawski

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 26, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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
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
Gradebook
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
Joined
Aug 23, 2010
Messages
16,867
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,570
Messages
5,707,141
Members
421,493
Latest member
Tusharrm

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top