Excel Primer

SUM & AVERAGE

Click a cell to see its formula, edit the numbers in column A, and watch B1 and B2 update.

Conditional logic: IF + COUNTIF

A common pattern: three grades per row, and a final grade that follows a rule — if any grade is MA, the final is MA; otherwise AR. Nested IFs get unreadable past two conditions, so this uses COUNTIF to ask "does MA appear at all?" instead. Try changing a grade in row 4 to MA.

Formula in column D: =IF(COUNTIF(A2:C2,"MA")>0,"MA","AR")

Weighted grades: SUMPRODUCT

Homework, tests, and a final each carry a different weight — SUMPRODUCT multiplies matching pairs then totals them in one step, instead of three helper columns.

=ROUND(SUMPRODUCT(B3:D3,B2:D2),1)

Letter grades: IFS & VLOOKUP

IFS checks conditions top to bottom — TRUE as the last one acts as a catch-all. VLOOKUP instead reads the grade off a boundary table (right) — better once cutoffs change often, since you edit the table, not the formula.

=IFS(A2>=90,"A",A2>=80,"B",A2>=70,"C",A2>=60,"D",TRUE,"F") · =VLOOKUP(A2,D3:E7,2)

Drop the lowest score: LARGE & SMALL

SMALL(range,1) is the lowest value in a range (SMALL(range,2) the 2nd-lowest); LARGE mirrors it from the top. Subtract the lowest before averaging to implement a "drop one quiz" policy.

Class stats: MEDIAN, MAX/MIN, STDEV

One bad test pulls the AVERAGE down further than the MEDIAN — useful for judging whether a single score is an outlier. STDEV shows how spread out the class is: low means everyone scored close together.

Filter by group: COUNTIFS, SUMIFS, COUNTBLANK

COUNTIFS/SUMIFS extend the COUNTIF pattern above with more range/criteria pairs — filter by section, homeroom, anything. COUNTBLANK flags rows nobody's graded yet.

Clean up rosters: TRIM, PROPER, TEXTJOIN

Pasted rosters are rarely clean. TRIM strips stray spaces, PROPER fixes capitalization, and TEXTJOIN stitches first + last back together with a chosen separator.

Shortcuts