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.
Task:
Expense claims are reimbursed up to a limit of 250 per claim. In C2:C6 give the amount actually paid: the claim, but never more than 250. Then in D2:D6, the bonus points for each claim, which are the claim minus 100 but never below zero.
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 | D | |
|---|---|---|---|---|
| 1 | Claim | Amount | Paid | Points |
| 2 | Train fares | 184 | ||
| 3 | Hotel | 312 | ||
| 4 | Client dinner | 96 | ||
| 5 | Conference | 450 | ||
| 6 | Taxi | 250 |
What this exercise teachesMay contain the answer
An IF would do the same job (=IF(B2>250,250,B2)), but it repeats the limit and the cell reference, and the two copies drift apart when the policy changes. MIN and MAX say the rule in one place: no more than this, no less than that.