EXTRACTING MORE THAN 1 LOWEST VALUE
Posted by Holans Rudi on February 21, 2001 7:34 AM
Does there exist a formula to extract the 3 lowest or highest values from a row. In addition, I also need to know the value (text) in the first row of the worksheet, right above the extracted value.
NAME A B C D E F
1 5 6 7 4 8 8
The result must be:
4 D 5 A 6 B
The function operates per row.
Many thanks for your support.
Rudi HOLANS
Posted by cpod on February 21, 2001 10:22 AM
Check Excel help for the small() and large() functions.
Posted by Aladin Akyurek on February 23, 2001 2:11 PM
Here is a formula-based solution.
Caveat: if your data contains duplicates and these show up in the subset of 3 lowest (highest) values, you will get the same text. Your example data have 3 highest values. What you get then will be:
8 E
8 E
7 C.
Here we go.
Assuming your labels occupy the range A1:F1 and your numeric values A2:F2.
Type
H1 =SMALL($A$2:$F$2,ROW()) [ copy down to H2:H3 to get 3 lowest values ]
G1 =INDIRECT(ADDRESS(1,COLUMN($A$2:$F$2)+MATCH(SMALL($A$2:$F$2,ROW()),$A$2:$F$2,0)-1)) [ copy down to G2:G3 to get labels corresponding to the 3 lowest values ]
Replace SMALL by LARGE to get 3 highest values and corresponding letters.
Hope this helps.
Aladin
