Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
location of cell
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: http://www.excelforum.com/member.php...o&userid=20916 View this thread: http://www.excelforum.com/showthread...hreadid=499654 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
location of cell
=3+MATCH(MAX(C4:C75),C4:C75,0)
will give the row -- HTH Bob Phillips (remove nothere from email address if mailing direct) "owl527" wrote in message ... 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: http://www.excelforum.com/member.php...o&userid=20916 View this thread: http://www.excelforum.com/showthread...hreadid=499654 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
location of cell
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: =("B"&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: http://www.excelforum.com/member.php...o&userid=20916 View this thread: http://www.excelforum.com/showthread...hreadid=499654 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
location of cell
=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) "owl527" wrote in message ... 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: =("B"&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: http://www.excelforum.com/member.php...o&userid=20916 View this thread: http://www.excelforum.com/showthread...hreadid=499654 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
location of cell
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: http://www.excelforum.com/member.php...o&userid=20916 View this thread: http://www.excelforum.com/showthread...hreadid=499654 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
location of cell
That 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) "owl527" wrote in message ... 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: http://www.excelforum.com/member.php...o&userid=20916 View this thread: http://www.excelforum.com/showthread...hreadid=499654 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
location of cell
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: http://tinyurl.com/44ywo owl527 wrote: 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: =("B"&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!!!! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
location of cell
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: http://www.excelforum.com/member.php...o&userid=20916 View this thread: http://www.excelforum.com/showthread...hreadid=499654 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
location of cell
You could try the pivot table approach to create Top 5 lists per country.
owl527 wrote: 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!!! -- [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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates of a Day for a month & year cell formulas | Excel Discussion (Misc queries) | |||
shortcut to go to previous location or cell | Excel Discussion (Misc queries) | |||
reporting back a cell location? | Excel Discussion (Misc queries) | |||
Urgent Cell location changes | Excel Discussion (Misc queries) | |||
Return cell contents based on conditional lookup | Excel Worksheet Functions |