returning a text cell based on a number cell
I'm having trouble finding a way to perform a function in excel. hopefully someone here can help me out. I have a spreadsheet with several hundred rows of data in it. Column A of each row is a text name, and Column B is a numerical value. At the end of the worksheet, I'd like to display the name and numerical value of the row with the lowest number in column B. I can't simply sort the entire worksheet though, because I'd like it to stay in alphabetical order. Returning the lowest numerical value is easy. I can just take the minimum of column B. What I'm having trouble with, is finding a way to return the text value in column A based on that number in B. Any ideas? THanks in advance Josh -- Josh7777777 ------------------------------------------------------------------------ Josh7777777's Profile: http://www.excelforum.com/member.php...o&userid=15996 View this thread: http://www.excelforum.com/showthread...hreadid=274631 |
Hi
=INDEX(A1:A100,MATCH(MIN(B1:B100),B1:B100,0)) -- Regards Frank Kabel Frankfurt, Germany "Josh7777777" schrieb im Newsbeitrag ... I'm having trouble finding a way to perform a function in excel. hopefully someone here can help me out. I have a spreadsheet with several hundred rows of data in it. Column A of each row is a text name, and Column B is a numerical value. At the end of the worksheet, I'd like to display the name and numerical value of the row with the lowest number in column B. I can't simply sort the entire worksheet though, because I'd like it to stay in alphabetical order. Returning the lowest numerical value is easy. I can just take the minimum of column B. What I'm having trouble with, is finding a way to return the text value in column A based on that number in B. Any ideas? THanks in advance Josh -- Josh7777777 --------------------------------------------------------------------- --- Josh7777777's Profile: http://www.excelforum.com/member.php...o&userid=15996 View this thread: http://www.excelforum.com/showthread...hreadid=274631 |
What you need is a Top N list, with N=1, based on Min value. Let A4:B11 house the following sample: {"Name","Score";"dawn",23;"damon",21;"bob",25;"chr is",22;"christine",21;"ian",32;"john",35} In C4 enter: Rank. In C5 enter & copy down: =RANK(B4,$B$4:$B$10,1)+COUNTIF(B4:$B$4,B4)-1 D1: =MIN(B5:B11) D2: 1 (the value of N in Top N) D3: =MAX(IF(INDEX(B5:B11,MATCH(D2,C5:C11,0))=B5:B11,C5 :C11))-D2 which must be confirmed with control+shift+enter instead of just with enter. Note that this formula calculates the number of the ties of the Min value. In D4 enter: Top List In D5 enter & copy down: =IF(ROW()-ROW(D$5)+1<=$D$2+$D$3,INDEX($A$5:$A$11,MATCH(ROW()-ROW($D$5)+1,$C$5:$C$11,0)),"") The Top List will consist of: {"damon";"christine"} If you're on Excel 2003, do the following... Change the rank formula in C4 to: =RANK(B5,$B$5:$B$14,1)+COUNTIF($B$5:OFFSET(B5,0,0) ,B5)-1 Select A4:D11. Activate Data|List|Create List. Check the "My list has headers" option. Click OK. Now, you don't have to adjust the ranges the formulas refer to and copying these formulas down. All this will happen fully automatically. Josh7777777 Wrote: I'm having trouble finding a way to perform a function in excel. hopefully someone here can help me out. I have a spreadsheet with several hundred rows of data in it. Column A of each row is a text name, and Column B is a numerical value. At the end of the worksheet, I'd like to display the name and numerical value of the row with the lowest number in column B. I can't simply sort the entire worksheet though, because I'd like it to stay in alphabetical order. Returning the lowest numerical value is easy. I can just take the minimum of column B. What I'm having trouble with, is finding a way to return the text value in column A based on that number in B. Any ideas? THanks in advance Josh -- Aladin Akyurek ------------------------------------------------------------------------ Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165 View this thread: http://www.excelforum.com/showthread...hreadid=274631 |
All times are GMT +1. The time now is 09:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com