ISERROR,SMALL,INDEX, MATCH, SMALL??
Let's use this example:
............E.....G
65.......X.....10
66.......Y.....20
67.......Z.....15
68.......A.....17
69.......B.....21
When that formula is copied down a column it returns the value from column E
that corresponds to the nth smallest value in column G.
The first expression:
=IF(ROW(A1)2,"",
Limits the number of values returned to 2. In the 3rd cell and each cell
the formula is copied to thereafter the result will be a blank cell. As you
drag copy the formula down a column the ROW(A1) function will incremnt like
this:
=IF(ROW(A1)2,"",
=IF(ROW(A2)2,"",
=IF(ROW(A3)2,"",
This evaluates to:
=IF(12,"",
=IF(22,"",
=IF(32,"",
The second expression:
IF(ISERROR(SMALL($G$65:$G$69,ROW(A1))),"",
is used to catch any errors if there might not be a nth smallest number in
column G. For example, if column G contained only a single number then:
SMALL($G$65:$G$69,ROW(A2))
would generate a #NUM! error because there is no 2nd smallest number in
column G. The ROW(A1) function within the SMALL function incremnts just the
same way that I described above.
SMALL($G$65:$G$69,ROW(A1)) = 1st smallest
SMALL($G$65:$G$69,ROW(A2)) = 2nd smallest
SMALL($G$65:$G$69,ROW(A3)) = 3rd smallest
So, if:
IF(ROW(A1)2 = FALSE *and*
IF(ISERROR(SMALL($G$65:$G$69,ROW(A1))) = FALSE
Then:
INDEX($E$65:$E$69,MATCH(SMALL($G$65:$G$69,ROW(A1)) ,$G$65:$G$69,0)))
Returns the value from column E that corresponds to the nth smallest value
in column G:
Based on the sample data above the results would be:
X (corresponds to 10 which is the 1st smallest value in column G)
Z (corresponds to 15 which is the 2nd smallest value in column G)
blank (is blank because: =IF(ROW(A3)2 : =IF(32, = TRUE)
blank (is blank because: =IF(ROW(A4)2 : =IF(42, = TRUE)
blank (is blank because: =IF(ROW(A5)2 : =IF(52, = TRUE)
etc
Biff
"M.A.Tyler" <Great Lakes State wrote in message
...
=IF(ROW(A1)2,"",IF(ISERROR(SMALL($G$65:$G$69,ROW( A1))),"",INDEX($E$65:$E$69,MATCH(SMALL($G$65:$G$69 ,ROW(A1)),$G$65:$G$69,0))))
Can someone dicipher this please?
Thanks,
M.A.Tyler
|