Working with mathematical formulas
A total can be found in three ways: adding as in =C2+D2+E2+F2, the function =SUM(C2:F2), or the Σ (AutoSum, Alt+=) button; then we copy the formula down and the relative reference adjusts for each row. Completion percentage = amount done · 100 : plan; we put an absolute reference on the plan cell: =F2*100/$F$1. To fill a number sequence with a step, select two cells and drag the fill handle, or in Home → Fill → Series give the step and stop value. For the linear equation ax + b = 0 with a ≠ 0, x = –b/a; in a sheet: =IF(A1=0; IF(B1=0; "any x"; "no solution"); -B1/A1). Excel stores a date as a number, so the difference of two dates (later − earlier) gives the number of days; the result cell must be set to the “General” or “Number” format. To make a table of function values, fill the x column with a step, write the y formula in the first row and copy it down.
A school garden plan was to plant 20 saplings and 14 are planted. The pupils calculated 14 · 100 : 20 = 70 per cent done and planned the rest of the work.
Project leaders, accountants and sports coaches calculate what percentage of a plan is done.
If today’s plan has 5 jobs and you have done 2, what percentage is done? Work it out.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 200 | ||||||
| 2 | 150 | 75 |
| x | –4 | –2 | 0 | 2 | 4 | 6 |
|---|---|---|---|---|---|---|
| y | –14 | –8 | –2 | 4 | 10 | 16 |
“Spreadsheet workshop”: a group builds a table of 3 months’ sales for 5 shops (made-up numbers) and calculates total, average and percentage of the plan for each; it checks how the formulas copy.