Excel track · Exercise 56 of 86 Paid
Capstone: North gross margin
Total by region, divide to a margin percentage, and leave a blank where revenue is missing.
Advanced
Revenue and cost by region
Read-only data · screen 1 of 2 · answer in B7
In B7, work out the North gross margin as a percentage, rounded to 1 decimal, and wrap it in IFERROR so an empty region shows a blank.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Revenue | Cost |
| 2 | North | 1500 | 800 |
| 3 | South | 1200 | 900 |
| 4 | North | 1000 | 580 |
| 5 | West | 900 | 700 |
| 6 | |||
| 7 | North gross margin % |
Open the workbook and read the gross margin in B7.
Preview the sheet
| A | B | C | |
|---|---|---|---|
| 1 | Region | Revenue | Cost |
| 2 | North | 1500 | 800 |
| 3 | South | 1200 | 900 |
| 4 | North | 1000 | 580 |
| 5 | West | 900 | 700 |
| 6 | |||
| 7 | North gross margin % | 44.8 |
The sheet computes B7 =IFERROR(ROUND((SUMIF(A2:A5,"North",B2:B5)-SUMIF(A2:A5,"North",C2:C5))/SUMIF(A2:A5,"North",B2:B5)*100,1),"")
Reference answer
What it does
SUMIF pulls the North rows out of each money column: revenue 1500 + 1000 comes to 2500 and cost 800 + 580 to 1380. Subtracting cost from revenue leaves 1120, and dividing that by revenue gives 0.448. Multiply by 100 and ROUND to one decimal for 44.8.
The reference answer nests three functions. SUMIF builds both totals, ROUND holds the margin at one decimal, and IFERROR wraps the result. A region with no revenue would divide by zero, and IFERROR(value,"") turns that error into a blank a chart can still plot.
Order inside the nest matters. ROUND sits inside IFERROR, so the guard wraps a finished number. Swapped, an empty region's division error reaches the cell before IFERROR can answer. Leave ROUND out and the margin arrives with a long tail of digits.
Syntax
=IFERROR(ROUND((SUMIF(regions,"North",revenue)-SUMIF(regions,"North",cost))/SUMIF(regions,"North",revenue)*100,1),"")Worked example
On Revenue and cost by region, =IFERROR(ROUND((SUMIF(A2:A5,"North",B2:B5)-SUMIF(A2:A5,"North",C2:C5))/SUMIF(A2:A5,"North",B2:B5)*100,1),"") returns 44.8.
=IFERROR(ROUND((SUMIF(A2:A5,"North",B2:B5)-SUMIF(A2:A5,"North",C2:C5))/SUMIF(A2:A5,"North",B2:B5)*100,1),"")Common errors
| Error | What it means and how to fix it |
|---|---|
#DIV/0! | A region with no revenue divides by zero, so the cell shows this until IFERROR catches it. |
#NAME? | North is typed without quotes, so Excel reads it as a name and returns #NAME?. |
40 for 44.8 | Rounding the ratio to one decimal before multiplying by 100 gives 40 instead of 44.8. |
At work
The region summary cell: two SUMIF totals, a ratio, a rounded figure and a blank where a region has no revenue.