Excel exercises
Excel exercises: Real workbooks
This group is four small workbooks where the formulas have to agree with each other.
Each one combines functions from earlier groups: VLOOKUP against a tier table, SUMPRODUCT over paired ranges, MAX with a zero guard, IF and COUNTIF for a checklist. No new functions arrive. What changes is the size of the question, and one range that starts a row late still produces a total. Checking the edges is most of the work.
Four exercises at level 3, each with two screens: build the sheet, then read a number back out of it.
The reading screen is the one to take slowly. A wrong total is often a right formula applied to a range that missed its last row, and a number read off the sheet will not say so. Recompute one line by hand and compare. The reorder exercise wraps each shortfall in MAX(0,C2-B2), so a row with a surplus asks for zero parts and not a negative number.
A stale exercise is one you finished two weeks ago or longer. One that needs practice took a hint, or more than one try.
0 of 4 complete
- A commission sheet that updates itself Paid Read a rate from a banded table, then multiply it by the sales figure.
- A headcount and payroll summary Paid Total a payroll across two columns, then divide it by the headcount.
- An inventory reorder report Paid Floor a row's shortfall at zero, then net the whole sheet the same way.
- A monthly close checklist in formulas Paid Flag a reconciliation from its variance, then count the rows still out.