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