Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Josh7777777
 
Posts: n/a
Default 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

  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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


  #3   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I want to type a text in a cell, but I want that text to represen. ExcelQ Excel Discussion (Misc queries) 1 January 21st 05 08:45 PM
inserting data from a row to a cell, when the row number is specified by a formula in a cell [email protected] New Users to Excel 2 January 6th 05 08:18 AM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM
how to hyperlink text to a cell steve New Users to Excel 1 December 14th 04 09:21 PM
I am trying to link based on a text value instead of cell position John Links and Linking in Excel 3 December 3rd 04 07:29 PM


All times are GMT +1. The time now is 10:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"