BeginnerStatistical Functions
Find the second-lowest quote with SMALL
Pick out the second-cheapest supplier quote rather than simply the cheapest.
SMALL
4 min10 points
SMALL
Returns the nth smallest value in a range.
Syntax
SMALL(array, k)
Statistical Functions.
Practise SMALL3 interactive exercises, free, no account needed
A worked example
=SMALL(C2:C9,2)
Returns $1,250
The second-smallest order value.
Where it goes wrong
Asking for a position beyond the number of values returns #NUM!.
What the exercises catch
Each of these is a wrong turn the practice exercises recognise and name. They are the misconceptions, not the answers.
- MIN gives you the lowest quote, which is the one procurement has ruled out. You need the second-lowest — a function that takes a position as well as a range.Find the second-lowest quote with SMALL
- That gives you the lead time — 7 days — but procurement asked who, not how long. The number still has to be turned back into a name.Name the third-fastest supplier
- Right number, typed rather than worked out. When a supplier revises their bid tomorrow this cell will quietly be wrong. Point a formula at the column instead.Find the second-lowest quote with SMALL
- Position 1 is the highest and lowest, so this is MAX minus MIN written the long way. Second from each end means position 2.Measure the spread, ignoring the extremes
Practise SMALL
Knowing what a function does and being able to write it from memory are different skills. These close the gap.
All SMALL practice exercises →
- IntermediateStatistical Functions
Measure the spread, ignoring the extremes
Compare the second-best and second-worst weeks to get a fairer range.
LARGE · SMALL
7 min25 points - AdvancedLookup & Reference
Name the third-fastest supplier
Return a name from one column based on a ranked value in another.
INDEX · MATCH · SMALL
13 min40 points