Inspect earned/possible totals and weighted contributions. Change one assignment among recorded, unrecorded, and authorized EX to see the denominator and eligibility change.
Declare the exercise policy
Microsoft's gradebook page describes native Excel templates with editable assignments, category weights, and formulas. Use that as workflow context; this article supplies an original specification rather than a downloaded template. Microsoft gradebook templates.
For the fictional exercise, choose these rules:
| Category ID | Category | Weight | Rate calculation |
|---|---|---|---|
| practice | Practice | 0.4 | Included earned points / included possible points |
| project | Project | 0.6 | Included earned points / included possible points |
The weights must total 1. Both categories must have positive included possible-point totals. Do not reweight the remaining category if one has no included points.
Within a category, use the ratio of point totals, not an arithmetic mean of assignment percentages. For this packet's complete-record policy, an unrecorded required score holds the overall result. EX excludes an assignment only when explicitly authorized under this supplied exercise policy; it is not a universal school rule.
Keep a blank reusable workbook specification separate from the labeled fictional worked example. Use stable student, assignment, and category IDs so that moving rows does not change which record a score belongs to.
Follow Student A's category contributions
| Fictional student | Assignment ID | Category | State | Earned | Possible |
|---|---|---|---|---|---|
| A | P1 | practice | Recorded | 8 | 10 |
| A | P2 | practice | Recorded | 16 | 20 |
| A | J1 | project | Recorded | 18 | 20 |
Practice totals are 24/30, giving a rate of 0.8. Project is 18/20, giving 0.9. The weighted contributions are 0.8 × 0.4 = 0.32 and 0.9 × 0.6 = 0.54. Their sum is 0.86, displayed as 86%.
The arithmetic mean of the three assignment percentages would be (80 + 80 + 90) / 3 ≈ 83.333%. That is a different calculation, not an alternative way to obtain the specified category-weighted result.
Show the included totals, category rates, weights, and contributions beside the overall answer. A reader should be able to reconstruct 86% without inspecting an unexplained formula cell.
Distinguish recorded zero, blank, and EX
For Student B, keep P2 at 16/20 and J1 at 18/20. Test P1 in three separate cases:
| P1 state | Practice calculation | Expected overall result |
|---|---|---|
| Recorded 0/10 | 16/30 | 75.333…% |
| Blank, unrecorded | Required score unresolved | HOLD |
| Authorized EX | 16/20; exclude P1's earned and possible points | 86% |
A recorded zero includes the assignment's ten possible points. The result is (16/30) × 0.4 + 0.9 × 0.6 = 0.753333…. A blank is not that recorded zero under this policy.
For EX, remove both the earned and possible quantities from the category aggregation. Leaving the ten possible points while excluding only the score would apply a different rule. Record the authorization state separately instead of placing the letters EX into a numeric points field.
If every Practice assignment is excluded, the category has no positive denominator. Hold the overall result; do not quietly turn Project's 60% weight into 100%. These choices belong to the declared fictional policy and need school confirmation before real use.
Gate the weighted formula with validation
Microsoft documents SUMPRODUCT as multiplying corresponding arrays and summing their products. Its array dimensions must match, and nonnumeric entries are treated as zero. That last behavior makes validation essential before score-state text reaches a numeric calculation. Microsoft SUMPRODUCT reference.
In the workbook, place validated category rates in B2:B3 and corresponding weights in C2:C3. Only when all record and policy checks are valid should =SUMPRODUCT(B2:B3,C2:C3) supply the weighted numeric result. Keep status and error reasons outside those numeric arrays.
Before calculation, check unique assignment definitions and one score record per student/assignment pair, recognized category IDs, valid score states, numeric earned and possible points, earned between zero and possible, positive possible points, and valid nonnegative weights totaling 1. This exercise requires both category denominators to be positive, including when a category weight is zero.
Any duplicate record, unresolved score, invalid number, inconsistent points, missing category denominator, or invalid weight configuration produces HOLD with a specific reason. Confirm actual formula references, Excel version, locale syntax, and recalculation behavior in the native workbook before accepting its output.
Try the worked example
Calculate the default 86%, then Recorded zero for 75.333333%. Unrecorded holds the total. Authorized EX removes P1’s earned and possible points together, giving 86%. Confirm the declared exercise policy before real school use. Blank is not recorded zero under this policy.
This is a local fictional-policy calculator, not an XLSX workbook or native Excel recalculation.
Recreate this example
Build one complete self-contained HTML page for this task: calculate the original fictional grade policy from category totals.
Use these exact original practice data and worked expectations: Practice P1 is 8/10 and P2 is 16/20; Project J1 is 18/20. Weights 0.4 and 0.6 give 86%. A recorded P1 zero gives 75.333333%; an unrecorded P1 holds calculation. Authorized EX excludes P1 earned and possible points, giving 86%.
Implement the following useful local interactions: Offer state buttons, P1 numeric earned field, two weights, and Calculate. Keep P2/J1 fixed. Validate score 0–10, nonnegative weights totaling 1, no silent reweighting. Show contributions and overall percentage. Preserve the supplied source text and distinguish an uncomputed state, a successful local result, and an invalid or incomplete input. Update output only from the current input snapshot. An input edit must retire stale feedback until the reader runs the relevant action again. Do not silently invent missing values or replace a failing case with a success.
Design the page as a compact source-and-result developer workbench with readable code, labeled inputs, and a separate explanation area. Put a compact task heading, actual sample data, the main result, and usable controls early enough to be useful on a narrow screen. Use readable text of at least 16px, generous paragraph spacing, strong contrast, native labels, accessible buttons, and visible keyboard focus. Stack long records at 360px and wrap code without page-level horizontal overflow.
Use type=button and explicit querySelector-based click handlers rather than relying on form submission. If Enter is useful, handle it only on the relevant input and ignore isComposing. Keep effects within this open page; no remote dependencies, network calls, persistence, fake downloads, or automated sending. This is a local fictional-policy calculator, not an XLSX workbook or native Excel recalculation. Place worked explanations beside their data and identify teacher or synthetic examples clearly. Output only the complete HTML document, with inline styles and script and LF line endings.
Review the fictional category-weighted calculator in NOS
Reproduce the zero, blank, and EX cases in your workbook before entering actual marks. Use the review explanation in NOS to compare why zero gives 75.333333%, blank holds the result, and authorized EX gives 86%; revise eligible static policy notes in visual editing mode before the workbook review.