Total, Average and Highest Monthly Spend
Your task: In E2:E4, write formulas for the total, the average and the highest monthly spend.
Full instructions, scenario and goal
Scenario
You manage the budget of a small design studio. The sheet lists what the studio has spent on software and tools in each month this year. June's invoices have not arrived yet, so June is still empty. Your manager wants three numbers for the budget meeting, and the sheet must keep working when June's figure is typed in.
Your task
- Look at the monthly spend in B2:B7. June (B7) is empty for now.
- In E2, calculate the total spent so far.
- In E3, calculate the average spend per month, using the months that have a figure.
- In E4, find the highest monthly spend.
- Write a formula in each of the three cells, then select Check Answer.
You are done when
- All three cells contain formulas that read the figures in column B.
- The average is based on the months that have a figure, not on a typed number of months.
- The formulas still work when June's figure is entered or when other figures change.
What you will practise
Summarise a column of figures with SUM, AVERAGE and MAX, and understand how Excel treats empty cells.
The idea in 30 seconds
SUM, AVERAGE and MAX are the three most-used summary functions. Each takes a range of cells and returns one number: the total, the mean, or the largest value. A range is written with a colon between its first and last cell, so the whole column of figures is one argument instead of a long chain of additions.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell E2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
SUM, AVERAGE and MAX are the three most-used summary functions. Each takes a range of cells and returns one number: the total, the mean, or the largest value. A range is written with a colon between its first and last cell, so the whole column of figures is one argument instead of a long chain of additions.
Excel's summary functions skip empty cells and text. That matters for AVERAGE: it divides by the number of cells that hold a number, not by the number of cells in the range. If you work out an average by dividing a total by a typed count, the answer drifts as soon as a blank is filled in or a row is added.
Skills you will practise
- SUM
- AVERAGE
- MAX
- Ranges
Common mistakes to avoid
- Dividing by a typed number of months
- A typed count is only right until the data changes. AVERAGE counts the figures for you.
- Stopping the range at the last filled cell
- A range that ends at B6 misses June when it arrives. Include the empty cell in the range.