tjdickinson
Board Regular
 Joined
 Jun 26, 2021
 Messages
 61
 Office Version

 365
 Platform

 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.
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:
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!
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
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!