software

Hi, I am trying to find the cell location of the largest number in a
colume. I put: =ROW(LARGE(C4:C75, 1))
it keeps saying my formula contains an error, what function can I use
to find out the cell location ? please let me know. Thanks.--
owl527
------------------------------------------------------------------------
owl527's Profile: www.excelforum.com/member.php...oamp;userid=20916
View this thread: www.excelforum.com/showthread...hreadid=499654=3 MATCH(MAX(C4:C75),C4:C75,0)

will give the row

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;owl527quot; gt; wrote in
message news
gt;
gt; Hi, I am trying to find the cell location of the largest number in a
gt; colume. I put: =ROW(LARGE(C4:C75, 1))
gt; it keeps saying my formula contains an error, what function can I use
gt; to find out the cell location ? please let me know. Thanks.
gt;
gt;
gt; --
gt; owl527
gt; ------------------------------------------------------------------------
gt; owl527's Profile:
www.excelforum.com/member.php...oamp;userid=20916
gt; View this thread: www.excelforum.com/showthread...hreadid=499654
gt;

Hi, not only do I want to find the largest number, I actually want to
find the top 5 numbers.
this is what I put:
=(quot;Bquot;amp;3 MATCH(MAX(C4:C75),C4:C75,0))
I want to get the value in this cell (not the number itself but the
category the number belongs to). it is giving me the cell location, I
am unable to display the value in the cell. How do I do that? PLEASE
HELP! thanks!!!!--
owl527
------------------------------------------------------------------------
owl527's Profile: www.excelforum.com/member.php...oamp;userid=20916
View this thread: www.excelforum.com/showthread...hreadid=499654=INDEX($B$4:$B$75,MATCH(LARGE($C$4:$C$75,ROW(A1)), $C$4:$C$75,0))

and copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;owl527quot; gt; wrote in
message news
gt;
gt; Hi, not only do I want to find the largest number, I actually want to
gt; find the top 5 numbers.
gt; this is what I put:
gt; =(quot;Bquot;amp;3 MATCH(MAX(C4:C75),C4:C75,0))
gt; I want to get the value in this cell (not the number itself but the
gt; category the number belongs to). it is giving me the cell location, I
gt; am unable to display the value in the cell. How do I do that? PLEASE
gt; HELP! thanks!!!!
gt;
gt;
gt; --
gt; owl527
gt; ------------------------------------------------------------------------
gt; owl527's Profile:
www.excelforum.com/member.php...oamp;userid=20916
gt; View this thread: www.excelforum.com/showthread...hreadid=499654
gt;

it works perfectly fine! thank you....
But, what does ROW(A1) do? I don't understand why you put it in the
formula. Please can you explain? thanks.--
owl527
------------------------------------------------------------------------
owl527's Profile: www.excelforum.com/member.php...oamp;userid=20916
View this thread: www.excelforum.com/showthread...hreadid=499654That is just to return an index into the large function. ROW(A1) returns 1,
so you get the first largest. When you copy down to the second row, this is
updated to ROW(A2), which returns 2, so you get the second largest.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;owl527quot; gt; wrote in
message news
gt;
gt; it works perfectly fine! thank you....
gt; But, what does ROW(A1) do? I don't understand why you put it in the
gt; formula. Please can you explain? thanks.
gt;
gt;
gt; --
gt; owl527
gt; ------------------------------------------------------------------------
gt; owl527's Profile:
www.excelforum.com/member.php...oamp;userid=20916
gt; View this thread: www.excelforum.com/showthread...hreadid=499654
gt;
You can obtain a correctly formed Top 5 list with:

1. a pivot table or
2. a formula system.

For the latter, see my post in:

tinyurl.com/44ywo

owl527 wrote:
gt; Hi, not only do I want to find the largest number, I actually want to
gt; find the top 5 numbers.
gt; this is what I put:
gt; =(quot;Bquot;amp;3 MATCH(MAX(C4:C75),C4:C75,0))
gt; I want to get the value in this cell (not the number itself but the
gt; category the number belongs to). it is giving me the cell location, I
gt; am unable to display the value in the cell. How do I do that? PLEASE
gt; HELP! thanks!!!!
gt;
gt;


Hi, this is similiar to what I am looking for, HOWEVER, I guess mine is
a bit more complicated because I have an extra column. e.g.
Column 1 - Countries (HK, US, UK)
Column 2 - Products (Banana, Apple, Orange, Grapes, Carrot, Potato,
Tomato)
Column 3 - values (numbers)

I would like to sort the top 5 product Per country.

Sample end result:
HK
Banana 240
Apple 137
Tomato 122
Orange 82
Apple 23

Please help! thanks!!!--
owl527
------------------------------------------------------------------------
owl527's Profile: www.excelforum.com/member.php...oamp;userid=20916
View this thread: www.excelforum.com/showthread...hreadid=499654You could try the pivot table approach to create Top 5 lists per country.

owl527 wrote:
gt; Hi, this is similiar to what I am looking for, HOWEVER, I guess mine is
gt; a bit more complicated because I have an extra column. e.g.
gt; Column 1 - Countries (HK, US, UK)
gt; Column 2 - Products (Banana, Apple, Orange, Grapes, Carrot, Potato,
gt; Tomato)
gt; Column 3 - values (numbers)
gt;
gt; I would like to sort the top 5 product Per country.
gt;
gt; Sample end result:
gt; HK
gt; Banana 240
gt; Apple 137
gt; Tomato 122
gt; Orange 82
gt; Apple 23
gt;
gt; Please help! thanks!!!
gt;
gt;

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.