SUM and AVERAGE

Adding =B2+C2+D2+E2 five times for five departments is tedious. SUM(B2:E2) plus the fill handle scales the whole school grid.

Column F on NorthgateBudget.xlsx will hold each department's annual total — English through Arts — in one consistent step.

A function is a built-in calculation. SUM adds a range; AVERAGE finds the mean.

In F1, type Annual Total (£) (replace the half-year label if needed). Click F2.

On the Home tab, click Σ AutoSum (or Formulas → AutoSum). Excel guesses =SUM(B2:E2). Press Enter.

Expected result: F2 shows 50500 for English.

Put the header in F1 first. Select F2 with quarterly data only in B2:E2. If AutoSum tries =SUM(B2:F2) — including the cell you are writing in — you selected the wrong cell.

Select F2. Drag the fill handle down to F6 — or double-click the fill handle.

Click F3. The formula bar should show =SUM(B3:E3) for Mathematics. Click F5 (Science). Formula bar: =SUM(B5:E5).

Expected result: Each department row has its own annual total. Science should be the largest near 72700.

=B2+C2+D2+E2 and =SUM(B2:E2) return the same number for English. SUM is shorter and harder to mis-count — you cannot accidentally skip D2.

In G1, type Avg Quarter (£). Click G2. Type =AVERAGE(B2:E2) and press Enter.

Expected result: G2 shows the mean of English's four quarters (12625). Copy G2 down to G6 with the fill handle.

After selecting B2:E2, look at the status bar — Excel may show Average without a formula. Status bar values are not saved; AVERAGE in a cell is.

If you leave Q4 blank for a new department, AVERAGE(B2:E2) divides by four but only three cells have numbers — the mean is too low. Fill all four quarters before averaging.

=SUM(B2:E2) in F2 totals all four quarters for English. =AVERAGE(B2:E2) in G2 divides that total by four.

Copying =SUM(B2:E2) from F2 to F3 gives =SUM(B3:E3) — relative references shift down one row. That behaviour is the foundation of the next lesson on $ absolute references.