Use the SMALL function to compare prices and select the cheapest offer - Basic Statistical Functions - Microsoft Excel Tutorials

Use the SMALL function to compare prices and select the cheapest offer using Microsoft Excel

A worksheet lists offers from different suppliers. To make a decision as to which is the best offer, the SMALL function can be used to check for the lowest price. As in the previous tip, you can use the INDEX and MATCH functions to get the names of the companies.

To find out the three cheapest offers and their supplier:

1. In cells B2:B10 enter the offers.
2. Number the cells C2:C4 with 1, 2, and 3.
3. Select cells D2:D4 and type the following formula: =INDEX($A$2:$A$10,MATCH(SMALL($B$2:$B$10,C2),$B$2:$B$10,0)).
4. Press <-Ctrl+Enter->.
5. Select cells E2:E4 and type the following formula: =SMALL($B$2:$B$10,C2).
6. Press <-Ctrl+Enter->.

Use the SMALL function to compare prices and select the cheapest offer

Use the SMALL function to find the smallest values in a list - Basic Statistical Functions - Microsoft Excel Tutorials

Use the SMALL function to find the smallest values in a list using Microsoft Excel

To determine the smallest value in a list, we can use the MIN function.  However, the easiest way to find multiple small values of a range is by using the SMALL function. This function returns the nth smallest value in a set of data.

SMALL(array, n)

array: An array or range of numerical data in which you want to find the nth smallest value.
n: The position from the smallest in the array or range of data to return.

To determine the three smallest values of a range:

1. In cells A1:A10 enter any values from 100 to 999.
2. Select cell C1 and type the following formula =SMALL($A$1:$A$10,1) to get the smallest value.
3. In cell C2 type the formula =SMALL($A$1:$A$10,2) to get the second smallest value.
4. In cell C3 type the formula =SMALL($A$1:$A$10,3) to get the third smallest value.

Use the SMALL function to find the smallest values in a list