Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
AVERAGEIFS. AVERAGEIFS averages only the rows that pass every test. The range to average comes first, then pairs of range and criteria, like SUMIFS. A row that fails any test is left out of both the total and the count.
=AVERAGEIFS(E2:E50,B2:B50,"Paris",C2:C50,"Paid") average paid Paris invoice
Marcus Hill wants the average size of a completed South order in early October, to compare with the East.
In G2, work out the average value (D2:D18) of orders whose region is the one in F2 and whose status is "completed". Round to 2 decimals.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Region | Status | Value | Region | Avg completed order | ||
| 2 | 50825 | South | completed | $288.90 | South | |||
| 3 | 50835 | East | completed | $168.15 | ||||
| 4 | 50836 | South | completed | $15.50 | ||||
| 5 | 50840 | East | completed | $153.00 | ||||
| 6 | 50841 | South | completed | $100.00 | ||||
| 7 | 50844 | South | completed | $461.50 | ||||
| 8 | 50849 | South | completed | $114.00 | ||||
| 9 | 50850 | South | completed | $265.83 | ||||
| 10 | 50853 | South | completed | $344.50 | ||||
| 11 | 50855 | East | completed | $626.64 | ||||
| 12 | 50857 | South | refunded | $59.40 | ||||
| 13 | 50861 | East | completed | $227.60 | ||||
| 14 | 50864 | South | completed | $539.00 | ||||
| 15 | 50865 | East | completed | $319.00 | ||||
| 16 | 50868 | South | completed | $101.50 | ||||
| 17 | 50873 | East | completed | $114.90 | ||||
| 18 | 50874 | South | completed | $167.00 | ||||
| 19 | ||||||||
| 20 | ||||||||
| 21 |
=IF(B2>=1.5,SUM(C2:C9),0)Language
Full plan
Excel ✓ · SQL and Power BI also included
Cancel anytime · 14-day full refund · Secure payment by Stripe