Practice by topic / Excel / Conditional Aggregation
AVERAGEIFS, MAXIFS and MINIFS work like SUMIFS: the values first, then one or more range/criteria pairs. =MAXIFS(C2:C7,A2:A7,"North") returns the highest North sale, and each extra pair narrows it further. They answer "what is the best, worst or typical value for this group?" without filtering. These 10 exercises build up step by step, starting with highest sale in one region, lowest sale in one region, average sale in a region, rounded.
3 of 10 free · no sign-up needed · graded on the formula you write
A2:A7 hold regions and C2:C7 sales. Write a formula in E2 that returns the highest sale in the North region.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | ||
| 2 | North | Ali | 4200 | ? | |
| 3 | South | Mei | 3100 | ||
| 4 | North | Raj | 5600 | ||
| 5 | East | Siti | 2800 | ||
| 6 | South | Tom | 4700 | ||
| 7 | North | Eva | 3900 |
Use =MAXIFS(C2:C7,A2:A7,"North")
Right — MAXIFS is MAX with a filter: the values first, then range/criteria pairs.
Expected result in E2: 5600
A2:A7 hold regions and C2:C7 sales. Write a formula in E2 that returns the lowest sale in the South region.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | ||
| 2 | North | Ali | 4200 | ? | |
| 3 | South | Mei | 3100 | ||
| 4 | North | Raj | 5600 | ||
| 5 | East | Siti | 2800 | ||
| 6 | South | Tom | 4700 | ||
| 7 | North | Eva | 3900 |
Use =MINIFS(C2:C7,A2:A7,"South")
Good — MINIFS takes its arguments in the same order as MAXIFS.
Expected result in E2: 3100
A2:A7 hold regions and C2:C7 sales. Write a formula in E2 that returns the average North sale, rounded to 2 decimal places.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | ||
| 2 | North | Ali | 4200 | ? | |
| 3 | South | Mei | 3100 | ||
| 4 | North | Raj | 5600 | ||
| 5 | East | Siti | 2800 | ||
| 6 | South | Tom | 4700 | ||
| 7 | North | Eva | 3900 |
Use =ROUND(AVERAGEIFS(C2:C7,A2:A7,"North"),2)
Right — wrap AVERAGEIFS in ROUND to tidy the result.
Expected result in E2: 4566.67
A2:A7 hold regions and C2:C7 sales. Write a formula in E2 that returns the highest North sale that is below 5000.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | ||
| 2 | North | Ali | 4200 | ? | |
| 3 | South | Mei | 3100 | ||
| 4 | North | Raj | 5600 | ||
| 5 | East | Siti | 2800 | ||
| 6 | South | Tom | 4700 | ||
| 7 | North | Eva | 3900 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2:A7 hold regions and C2:C7 sales. Write a formula in E2 that returns the average sale for every region except East.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | ||
| 2 | North | Ali | 4200 | ? | |
| 3 | South | Mei | 3100 | ||
| 4 | North | Raj | 5600 | ||
| 5 | East | Siti | 2800 | ||
| 6 | South | Tom | 4700 | ||
| 7 | North | Eva | 3900 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2:A7 hold regions, C2:C7 sales, and F1 holds a region name. Write a formula in F2 that returns the highest sale for the region in F1.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | Region | South | |
| 2 | North | Ali | 4200 | ? | ||
| 3 | South | Mei | 3100 | |||
| 4 | North | Raj | 5600 | |||
| 5 | East | Siti | 2800 | |||
| 6 | South | Tom | 4700 | |||
| 7 | North | Eva | 3900 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2:A7 hold test scores, where 0 means the student didn't sit the test. Write a formula in C1 that returns the lowest score above zero.
| A | B | C | |
|---|---|---|---|
| 1 | Score | ? | |
| 2 | 0 | ||
| 3 | 72 | ||
| 4 | 0 | ||
| 5 | 65 | ||
| 6 | 88 | ||
| 7 | 59 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2:A6 hold ticket priorities and B2:B6 the days each took to close. Write a formula in D1 that returns the average days to close for "High" priority tickets.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Priority | Days to close | ? | |
| 2 | High | 2 | ||
| 3 | Low | 9 | ||
| 4 | High | 4 | ||
| 5 | Medium | 6 | ||
| 6 | High | 3 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2:A7 hold customer names and B2:B7 order numbers (higher is newer). Write a formula in D1 that returns Acme's most recent order number.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | Order no. | ? | |
| 2 | Acme | 1001 | ||
| 3 | Bolt | 1002 | ||
| 4 | Acme | 1005 | ||
| 5 | Core | 1007 | ||
| 6 | Acme | 1009 | ||
| 7 | Bolt | 1010 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2:A6 hold part names, B2:B6 suppliers and C2:C6 prices. Write a formula in E1 that returns the cheapest price for "Bolt M6".
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Part | Supplier | Price | ? | |
| 2 | Bolt M6 | Alpha | 0.42 | ||
| 3 | Bolt M6 | Beta | 0.38 | ||
| 4 | Nut M6 | Alpha | 0.15 | ||
| 5 | Bolt M6 | Gamma | 0.4 | ||
| 6 | Nut M6 | Beta | 0.12 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →