 |
 | 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 / Worksheet Functions / February 2008Excel Question
Here is my problem. Say I have a list of 20 baseball player names in one column and how many home runs that they have in the next column. How can I make a cell = all the players names that have over 20 home runs? Please help. Thank you. One way: A1:A20 = player B1:B20 = runs Enter this array formula** in D1 and copy down to D20: =IF(ROWS(D$1:D1)<=COUNTIF(runs,20),INDEX(player,SMALL(IF(runs=20,ROW(player)-MIN(ROW(player))+1),ROWS(D$1:D1))),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)  SignatureBiff Microsoft Excel MVP > Here is my problem. > [quoted text clipped - 5 lines] > Please help. > Thank you. Ooops! >over 20 home runs Change the formula to: =IF(ROWS(D$1:D1)<=COUNTIF(runs,">="20),INDEX(player,SMALL(IF(runs>=20,ROW(player)-MIN(ROW(player))+1),ROWS(D$1:D1))),"") SignatureBiff Microsoft Excel MVP > One way: > [quoted text clipped - 17 lines] >> Please help. >> Thank you. wow!!! SignatureRyGuy > Ooops! > [quoted text clipped - 25 lines] > >> Please help. > >> Thank you. > wow!!! It's nice to know how to do this but a filter is sooooo much easier *but* there are times when you may want *dynamic* capabilities. SignatureBiff Microsoft Excel MVP > wow!!! > [quoted text clipped - 29 lines] >> >> Please help. >> >> Thank you. |
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.