Loading exercise…
Loading exercise…
Exercises are built for a keyboard. Read the task here, then open this page on a laptop to solve it.
Marcus Hill asks for December's unusually large completed orders: anything worth more than R1 times the average completed order. He wants one readable formula he can hand to the next analyst.
In I2, return every row of A2:G32 that is completed (Status "completed") and whose value is greater than R1 times the average value of completed orders. Use LET to name the average and the completed condition, and finish with FILTER. Point at R1. The headers are in I1:O1.
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order ID | Order date | Customer | Region | Channel | Status | Value | Order ID | Order date | Customer | Region | Channel | Status | Value | Times average | 1.5 | ||
| 2 | 51006 | 2025-12-01 | Pioneer Consulting | North | Sales Rep | completed | $371.80 | |||||||||||
| 3 | 51007 | 2025-12-01 | Willow Studios | North | Sales Rep | completed | $157.50 | |||||||||||
| 4 | 51008 | 2025-12-01 | Harbor Labs | East | Online | completed | $320.45 | |||||||||||
| 5 | 51009 | 2025-12-01 | Falcon Consulting | North | Sales Rep | completed | $42.00 | |||||||||||
| 6 | 51010 | 2025-12-02 | Beacon Hotels | East | Partner | completed | $275.60 | |||||||||||
| 7 | 51011 | 2025-12-02 | Willow Law Group | East | Online | completed | $224.00 | |||||||||||
| 8 | 51012 | 2025-12-02 | Copper Media | South | Online | completed | $251.55 | |||||||||||
| 9 | 51013 | 2025-12-03 | Willow Studios | North | Sales Rep | completed | $29.70 | |||||||||||
| 10 | 51014 | 2025-12-03 | River Print Co | West | Online | completed | $182.50 | |||||||||||
| 11 | 51015 | 2025-12-03 | Maple Academy | East | Sales Rep | completed | $841.60 | |||||||||||
| 12 | 51016 | 2025-12-03 | Willow Academy | North | Online | completed | $181.00 | |||||||||||
| 13 | 51017 | 2025-12-04 | Orchard Media | West | Online | completed | $74.00 | |||||||||||
| 14 | 51018 | 2025-12-04 | Willow Hotels | West | Sales Rep | completed | $86.40 | |||||||||||
| 15 | 51019 | 2025-12-04 | Copper Media | South | Online | completed | $148.50 | |||||||||||
| 16 | 51020 | 2025-12-04 | Bright Hotels | East | Sales Rep | completed | $118.50 | |||||||||||
| 17 | 51021 | 2025-12-05 | Harbor Fitness | South | Sales Rep | completed | $152.40 | |||||||||||
| 18 | 51022 | 2025-12-05 | Copper Media | South | Online | completed | $175.00 | |||||||||||
| 19 | 51023 | 2025-12-05 | Orchard Clinic | South | Online | cancelled | $823.00 | |||||||||||
| 20 | 51024 | 2025-12-05 | Copper Fitness | South | Sales Rep | completed | $223.00 | |||||||||||
| 21 | 51025 | 2025-12-06 | Pioneer Realty | West | Sales Rep | completed | $195.70 | |||||||||||
| 22 | 51026 | 2025-12-08 | Blue Accounting | North | Online | completed | $92.40 | |||||||||||
| 23 | 51027 | 2025-12-08 | Willow Hotels | West | Sales Rep | completed | $248.47 | |||||||||||
| 24 | 51028 | 2025-12-08 | Beacon Architects | North | Online | completed | $219.60 | |||||||||||
| 25 | 51029 | 2025-12-09 | Orchard Academy | South | Online | completed | $402.55 | |||||||||||
| 26 | 51030 | 2025-12-09 | Copper Media | South | Online | completed | $169.50 | |||||||||||
| 27 | 51031 | 2025-12-09 | Harbor Consulting | North | Online | completed | $747.83 | |||||||||||
| 28 | 51032 | 2025-12-10 | North Architects | South | Partner | completed | $19.80 | |||||||||||
| 29 | 51033 | 2025-12-10 | Cedar Realty | North | Sales Rep | completed | $122.50 | |||||||||||
| 30 | 51034 | 2025-12-10 | Harbor Hotels | West | Partner | completed | $182.00 | |||||||||||
| 31 | 51035 | 2025-12-10 | Harbor Studios | East | Online | refunded | $504.00 | |||||||||||
| 32 | 51036 | 2025-12-10 | Pioneer Realty | West | Online | completed | $105.00 | |||||||||||
| 33 | ||||||||||||||||||
| 34 | ||||||||||||||||||
| 35 |
=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