While using Excel, you can use an array formula in order to get 2nd, 3rd, and so on smallest values for the selected data range. For this purpose, SMALL and IF functions are used.
The formula has been applied in the cell G7 for the demonstrated example.
In the given example, data range considered for “Time” is from D3 to D15 whereas for “Sex” it is in the range of C3 to C15. The array formula can be used by simply entering Control + Shift + Enter.
Example and how this formula works
There comes a problem with this formula that SMALL is not always had to be operated on every value in the selected range and rather it is required to be applied (in this example) on Male or Female. IF function has been applied in the formula to apply this criterion and to check for a logical test for two of the given options i.e. Male (M) or Female (F). The result will be an array due to the fact that the test has been applied to an array of values.
In the given examples, the array is like this:
The number shows female times while on the other hand male times are represented by FALSE. TRUE and FALSE values will be ignored through SMALL function automatically and hence nth smallest value will be the result that will be from the set of actual numbers that come from an array.
Figure1. Example of nth smallest value with criteria