Spreadsheets II — Formulas, Functions, and Charts — Handout

Functions: SUM, AVERAGE, MAX, MIN

A colon inside the brackets means “through” — the same range you’d get by dragging from the first cell to the last. All four quietly skip genuinely blank cells rather than treating them as zero.

IF — a formula that decides

=IF(condition, if true, if false). The condition is usually a comparison: B2>=50. Text you want displayed (not a cell reference) goes in double quotes — =IF(B2>=50,"Pass","Fail"). The two branches can be numbers instead of text, or even other formulas.

Comparisons available: = equals, <> not equal, <, >, <=, >=.

Copying a formula: relative vs. absolute references

Select a cell, Copy (Ctrl+C), select a target cell or range, Paste (Ctrl+V). By default every reference in the formula is relative — it shifts by exactly how far the formula moved. Copy =B2*C2 from row 2 down to row 4, and it becomes =B4*C4 on its own.

A $ before the column letter or row number locks that half so it never shifts — $E$2 stays $E$2 however far the formula is copied. This is exactly what a shared value — a tax rate, a unit price, a pass mark — needs: every row gets its own data, but they all point at the one shared cell.

Sorting and filtering

Sorting reorders the rows of a selected range by one column, ascending or descending — nothing is deleted, rows just swap places, keeping each row’s data together. Filtering hides rows that don’t match a chosen value in one column; the data underneath is untouched, and clearing the filter brings every row straight back.

Choosing a chart type

A rough rule that covers most real cases: a trend over time (sales by month, profit by year) → line chart. Parts of one whole (how spending splits across categories) → pie chart. Comparing separate categories side by side (five students’ scores) → bar chart. It’s a guide, not a law — but it’s right far more often than it’s wrong.