Design advice on how to approach adding to a form and sheet (no coding only advice)

[DeezNuts]

Currently with a fair amount of help I have built what is a very nice student grade book. I need to expand what it does and not sure how to incorporate it.

Currently you input the assignment name, date, assignment type, possible points, and received points which works great but

Some of the courses do use weighted averages so there is possible and received points. Was easy to do when it was say 8 out of 10 or 18 out of 20 but now I need to add something that will work in this fashion
Coursework 20%
Drafts 22%
Final 45%
Records 8%
Exams 5%

No worries on the math aspect of this it comes down to the layout and the userform

What would be the best approach to add it to this? Was thinking the tab option to select standard or weighted and have this form on one and the weighted on the other. As for the sheet I was thinking the grade would go in the same location.

Or would it be more practical to figure out a formula that if assignment type is 20% just do the formula at the top where the totals are. Using sumproducts sums and ifs off in a hidden area. Then in this form I would just input as normal but instead of points just use say 97% out of 100%.

Call this poor planning built this without even thinking about weighted so not looking for coding help just advice on how to make this a dual sheet to work with both.

 

[pdevito3]

So I'm not sure I fully getting what you are asking, but from what I got out of this is that you need to incorporate an "Assignment Type" (Coursework, Draft, Exam, etc.) and "Weight" (20,22,5 respectively) to each Assignment and then multiply the Received points by that weighted value to get the "actual" grade, correct?

If this is the case, I would probably have a master list with the weights, or hard code them to your VBA and then have another outputted field that says "Weighted Grade," or something of the sort. If a class is more straight forward or doesn't weigh grades then the weighted grade would be just be the actual grade. This would require very little modification, if any, modification to your userform. Just the worksheet and the code.

Regardless, I like the setup, looks good.

Just a last parting note: if you haven't already thought of this, I would suggest keeping all of this data on a separate sheet as well in more of a database table as well. This means just rows and columns, nothing pretty just a straight row with all of the primary and metadata fields for each entry. This would just require the code to add a new entry, or modify an existing entry, as items are entered/modified. This makes the data more easily queried and analyzed. Something like this is normally done in something like Access, but since it's already built in Excel, you can probably still pull it off here.

Hope this helps

 

[DeezNuts]

thank you, currently this system uses the 10/10 style grading but the other style is on the weighted percentage so it would be like 97%/100% but that assignment might only be worth 22% of the grade and there could more than one assignment in that 22% I made a weighted on but different style and layout and need to incorporate it into this new version think I will try with formulas and hide all the formulas in a separate page to keep it clean to get an idea here is the weighted version I did

 

[pdevito3]

Gotcha, in the future, I would suggest a normalized database design. If you're using excel, you don't necessarily need keys or multiple tables, but something like the below. Also, in your case, you could consider multiplying the actual and possible points by the weight to account for the change. Multiply by 1 if non weighted.

Student Name

Class

Teacher

Assignment

Assignment Type

Weight

Due Date

Actual

Possible

<tbody>
</tbody>

Ideally, a lot of these would be pull downs and maybe vlookups/indexes to prevent bad data.