 |
 | 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 / October 2007Find the 10 lowest of 20 numbers in column and enter those 10 in a
Seeing all the Excel experts out here is giving me hope to solve this. I am trying to find the 10 lowest (and if possible highlight them) of 20 numbers in a column and enter the results in the next column to the right. There will be 6 columns total and 20 rows. ADate BData Cdata Ddata Edata FData The E Column is where I am looking to find the lowest 10 of 20 numbers and the F Column is where I am looking to put those results. Thank you in advance for any help you can give. Dan  Signatureshredder Maybe =SMALL($E$1:$E$20,ROW(A1)) Put this in F where you want the result and drag down 10 rows. Mike > Seeing all the Excel experts out here is giving me hope to solve this. > I am trying to find the 10 lowest (and if possible highlight them) of 20 [quoted text clipped - 9 lines] > > Dan Thanks Mike. Sorry I didn't even make you break a sweat on that one! Works great. My data is actually in E2 : E21 but that adjustment was easy. Can I ask what the (A1)) does in this formula? Because the A1 in my worksheet is a "date" header. Thanks again Signatureshredder > Maybe > [quoted text clipped - 17 lines] > > > > Dan The ROW(A1) function returns a result of 1, and when you fill it down the next row down will have ROW(A2) returning 2, and so on. SignatureDavid Biddulph > Thanks Mike. Sorry I didn't even make you break a sweat on that one! Works > great. [quoted text clipped - 28 lines] >> > >> > Dan In F1: =SMALL($E$1:$E$20,ROW()) Copy down to F10 For highlighting Column E: Select E1:E20 Format>Conditional Formatting Choose Formula is enter this formula =COUNTIF($E$1:$E$20,F1) Click Format On the Patterns tab, select a background color. Click OK several times SignatureKind regards, Niek Otten Microsoft MVP - Excel | Seeing all the Excel experts out here is giving me hope to solve this. | I am trying to find the 10 lowest (and if possible highlight them) of 20 [quoted text clipped - 9 lines] | | Dan Thanks for the reply Nick. I am having trouble with the highlighting portion of the post. My data is in E2 thru E21 and when I enter your formula it only highlights E3 thru E12 regardless of the number hierarchy. What I am trying to do is, highlight the lowest 10 of the 20 numbers in the column. I double checked everything and the formula is entered as shown in the post. The formula you gave for the finding and posting of the lowest 10 of 20 numbers worked with the mod (A1)) at the end. Look at Mikes reply and it will show that. Not sure why it works that way, but that is why I'm here to learn. Thanks again for any help you can give on this. Dan Signatureshredder > In F1: > [quoted text clipped - 33 lines] > | > | Dan To highlight the 10 smallest numbers: Select the range E2:E21 Goto the menu Format>Conditional Formatting Formula Is: =AND(E2<>"",E2<=SMALL(E$2:E$21,10)) Click the Format button Select the style(s) desired OK out Note that there could be more or less than 10 numbers highlighted depending on duplicates and how many numbers you actually have in the range. SignatureBiff Microsoft Excel MVP > Thanks for the reply Nick. > I am having trouble with the highlighting portion of the post. My data is [quoted text clipped - 56 lines] >> | >> | Dan Thanks Biff! Worked like a charm.. and by the way. Where is a good source to learn how to formulate this stuff. I have some excel books but hard to pick out this sort of thing from the book. Signatureshredder > To highlight the 10 smallest numbers: > [quoted text clipped - 71 lines] > >> | > >> | Dan >Where is a good source to learn how to formulate this stuff. >I have some excel books but hard to pick out this sort >of thing from the book. Yeah, I know what you mean! You'll learn more in these forums than you will from a book! All you have to do is invest the time! Read the posts and study the replies. Try applying the solutions to your own applications. Keep a library of formulas for quick reference. SignatureBiff Microsoft Excel MVP > Thanks Biff! Worked like a charm.. and by the way. Where is a good source > to [quoted text clipped - 85 lines] >> >> | >> >> | Dan Biff is absolutely right! It's so hard to find the answer in a "book" when you don't even know what you should be looking for in the first place. All I can say is "Thank God for all you Excel EXPERTS out there!! SignatureJM > >Where is a good source to learn how to formulate this stuff. > >I have some excel books but hard to pick out this sort [quoted text clipped - 96 lines] > >> >> | > >> >> | Dan I hear that JM! Signatureshredder > Biff is absolutely right! It's so hard to find the answer in a "book" when > you don't even know what you should be looking for in the first place. All I [quoted text clipped - 100 lines] > > >> >> | > > >> >> | Dan I think you are right. It does take some time but you and a lot of others here sure know your stuff, and I'm certainly glad of that.. If I keep pullin' my hair out trying to figure this stuff out, I will be a very patchy bald guy! Any thoughts on the Excel 2003 bible from Wallenbach ( think that's his name) ? Signatureshredder > >Where is a good source to learn how to formulate this stuff. > >I have some excel books but hard to pick out this sort [quoted text clipped - 96 lines] > >> >> | > >> >> | Dan > Any thoughts on the Excel 2003 bible from Wallenbach ( think that's his > name) ? John Walkenbach. He's a highly respected author and his books are recommended very often in these forums. The best way to choose a book is to go to a bookstore and thumb through the selections. I think the Bible is a general overview of the entire Excel application. He also has books that are for a specific aspect of Excel. For instance there is a book that is *all* about formulas. There are others that are *all* about VBA programming and charting. Here's a good listing of Excel and related books: http://contextures.com/xlbooks.html SignatureBiff Microsoft Excel MVP >I think you are right. It does take some time but you and a lot of others > here sure know your stuff, and I'm certainly glad of that.. If I keep [quoted text clipped - 116 lines] >> >> >> | >> >> >> | Dan Thank you Mr.Biff. I appreciate all your information! Signatureshredder > > Any thoughts on the Excel 2003 bible from Wallenbach ( think that's his > > name) ? [quoted text clipped - 132 lines] > >> >> >> | > >> >> >> | Dan You're welcome! SignatureBiff Microsoft Excel MVP > Thank you Mr.Biff. I appreciate all your information! > [quoted text clipped - 148 lines] >> >> >> >> | >> >> >> >> | Dan |
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.