 |
 | 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 2008Finding 1st,2nd,3rd etc
NAME AMOUNT MAX 20 CLIFF 5 CLIFF 38 MAX 40 CLIFF 60 DOT 50 CLIFF 60 CLIFF 60 CLIFF 78 CLIFF 60 DOT 44 I have a data table with names and scores. Sometimes the names are repeated. Cliff is repeated 4 times. Does anyone knows a formula that will tell me that Cliff's lowest score is 5 and his second to lowest score is 38? 60 would be his 3rd lowest and 78 would be his 4th. I tried using small, but small indicates that 60 is the 3rd , 4th and 5th lowest with 78 being the 6th . In reality, 78 is not his 6th lowest but his 3rd. =SMALL(IF(A2:A20="CLIFF",B2:B20),1) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. Replace ,1 with ,2 etc.  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > NAME AMOUNT > MAX 20 [quoted text clipped - 18 lines] > . > In reality, 78 is not his 6th lowest but his 3rd. It works like a charm Oops. I retested the formula but 60 was stillshown to be the 3rd , 4th and 5th lowest with 78 being the 6th . Do you know anyway around this Bob? If the Names are in A2:A12 and Amounts in B2:B12 then put your name [Cliff] in D1 then this formula in D2 copied down will give you the successive lowest values for that name with no repeats =MIN(IF(A$2:A$12=D$1,IF(ISNA(MATCH(B$2:B$12,D$1:D1,0)),B$2:B$12))) This is an array formula which needs to be confirmed with CTRL+SHIFT+ENTER Note: once amounts are exhausted for that name you get zeroes..... > NAME AMOUNT > MAX 20 [quoted text clipped - 14 lines] > indicates that 60 is the 3rd , 4th and 5th lowest with 78 being the 6th . > In reality, 78 is not his 6th lowest but his 3rd. Ive tried so many formulas since yesterday. Thanks |
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.