ISERROR,SMALL,INDEX, MATCH, SMALL??
=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 |
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 |
All times are GMT +1. The time now is 11:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com