Statistical FunctionsIntermediate

Award the Second-Lowest Bid with SMALL

LARGE and SMALL pull the kth-ranked value straight out of a range without sorting it, which is exactly what a purchasing rule written as "second-lowest, not lowest" needs.

Task:

You run bid evaluation for a city public-works office that just opened six sealed bids to repave a parking lot. Purchasing policy doesn't award the lowest bid outright — a bid far below the rest is often a sign the contractor underbid and will chase change orders once the work starts, so the contract goes to the second-lowest bid instead. In B8, use SMALL to find that second-lowest bid among the six in B2:B7. In B9, use LARGE and SMALL together to find the spread between the highest and lowest bid, so the review committee can see how wide the range was.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

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.

9 rows × 2 columns2 cells you fill in
AB
1ContractorBid ($)
2Sunrise Paving184500
3Bedrock Construction179200
4Highline Asphalt191750
5Metro Surface Co176800
6Granite Peak Builders198300
7Coastal Roadworks182100
8Second-lowest bid (awarded)
9Bid spread (highest minus lowest)
What this exercise teachesMay contain the answer

SMALL(B2:B7,2) is the purchasing policy read as a formula: not "the cheapest bid" — rank 1 — but "the cheapest bid once the actual cheapest is set aside", rank 2 counting up from the bottom. Metro Surface Co's $176,800 is the lowest of the six and takes no part in the award; Bedrock Construction's $179,200 is the next bid up, and that's the value SMALL returns. The spread works the same way from both ends of the same ranked list: LARGE(B2:B7,1) is the single highest bid, SMALL(B2:B7,1) is the single lowest, and subtracting one from the other needs no sorted column or helper cells, because both functions already found their value by rank rather than by where it happens to sit in the sheet.