MINIFS and MAXIFS: the extremes of one group
The cheapest and dearest option you can actually buy today.
Task:
You are pricing a replacement monitor. Some suppliers are out of stock, and their prices are no use to you. Put the lowest in-stock price in B9 and the highest in-stock price in B10.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of MIN, MAX
0/4The same formula in the other shapes it takes at work.
- MIN and MAX Functions
Find the minimum and maximum values in a dataset.
CoreBeginner - MINIFS and MAXIFS: the extremes of one group
The cheapest and dearest option you can actually buy today.
Intermediate - The spread: MAX minus MIN
How far apart the best and worst figures are, in one cell.
Beginner - Cap and floor a value with MIN and MAX
MIN as a ceiling and MAX as a floor — the most common use of both at work.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | |
|---|---|---|---|
| 1 | Supplier | Price | In stock |
| 2 | Northline | 189 | Yes |
| 3 | Brightway | 164 | No |
| 4 | Coastal IT | 212 | Yes |
| 5 | Datafirst | 175 | Yes |
| 6 | Elmside | 238 | No |
| 7 | Fernhill | 199 | Yes |
| 8 | |||
| 9 | Cheapest in stock | ||
| 10 | Dearest in stock |
What this exercise teachesMay contain the answer
Plain MIN would report Brightway at 164, which you cannot buy. MINIFS and MAXIFS apply the condition first and only then look for the extreme, so the answer is the cheapest thing that is actually available. Note the order: unlike AVERAGEIF, the range you want back comes first.