Statistical Functions
Intermediate
The lowest price that is not zero
A zero meaning "no quote" should not win the cheapest-price contest.
Task:
Suppliers that did not quote are shown as 0 in the price column. In B9 give the lowest actual quote.
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/3The same formula in the other shapes it takes at work.
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 | |
|---|---|---|
| 1 | Supplier | Quote |
| 2 | Avon Parts | 412 |
| 3 | Barrow Ltd | 0 |
| 4 | Cleeve & Co | 389 |
| 5 | Dart Supply | 455 |
| 6 | Exe Trading | 0 |
| 7 | Fowey Ltd | 398 |
| 8 | ||
| 9 | Lowest quote |
What this exercise teachesMay contain the answer
Zeros used as placeholders are a classic trap for MIN and AVERAGE alike. Excluding them with a condition is safer than deleting them, because the zero may mean something to whoever filled the sheet in — here, that a supplier was asked and declined.