 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 20081st smallest, 2nd smallest,3rd smallest
I have a list of numbers in A1 to A5 4 4 5 8 10 I am trying to find the 1st smallest, 2nd smallest,3rd smallest etc number. Using the formula =SMALL(A1:A5,2), the second smallest returns a 4 but I really want it to return 5. I want to ignore duplicates. I tried RANK since RANK ignores duplicates but it only returns the cell position. Does anyone knows how to solve this enigma? Thanks in advance. [snip] > I tried RANK since > RANK ignores duplicates but it only returns the cell position. Does anyone > knows how to solve this enigma? > Thanks in advance. ?? Rank returns a number. Disregard my reply. I'm still not quite awake. ;) When I use =RANK(5,A1:A5,1) the answer I get is 3. which is the third POSITION not the value. ( I expect an answer of 2 because 5 is the second smallest number while 4 is the first ). But since there are two instances of 4 the formula assumes that the two fours make up for the 1st and second smallest number so that 5 is the third smallest. SMALL() is getting fooled by the duplicates. So let's get rid of them: In B1 enter 1 In B2 enter: =IF(A2="",-1,IF(COUNTIF($A$1:$A1,A2)>0,0,MAX($B$1:B1)+1)) and copy down In C1 enter: =INDEX($A$1:$A$30,MATCH(ROW(),B$1:B$30,0),1) and copy down This is what we see: 4 1 4 4 0 5 5 2 8 8 3 10 10 4 Note that column C is just column A with all the duplicates removed. Now =SMALL(C:C,1) shows 4 =SMALL(C:C,2) shows 5 =SMALL(C:C,3) shows 8  SignatureGary''s Student - gsnu200787 > When I use =RANK(5,A1:A5,1) the answer I get is 3. which is the third > POSITION not the value. ( I expect an answer of 2 because 5 is the second > smallest number while 4 is the first ). But since there are two instances of > 4 the formula assumes that the two fours make up for the 1st and second > smallest number so that 5 is the third smallest. Try this: Enter this formula in D1: =MIN(A1:A5) Enter this array formula** in D2 and copy down until you get blanks: =IF(D1=MAX(A$1:A$5),"",MIN(IF(A$1:A$5>D1,A$1:A$5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) SignatureBiff Microsoft Excel MVP >I have a list of numbers in A1 to A5 > 4 [quoted text clipped - 10 lines] > knows how to solve this enigma? > Thanks in advance. Thanks! Greaat! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.