| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting to the address
Have been using excel for some years now (self taught) and there are a few occasions where things drive me nuts. Usually I sort them out, but this one has beaten me before and has cropped up again to haunt me. It goes somthing like this. I have a column of results of which I can extract the highest result. No problems there. But I also wish to see if that result is repeated. Again no problem there, using "Large". I can locate the first result using "Match" to find the row,then add one to this to start the search again from the last result +1. Ok, still with me. So I have my new range, but do not know how to use it. Have looked at many, many web sites and tried nearly all the most likley functions in excel. All except the right one. HELP PLEASE. Keenasmustard -- keenasmustard ------------------------------------------------------------------------ keenasmustard's Profile: http://www.excelforum.com/member.php...o&userid=31524 View this thread: http://www.excelforum.com/showthread...hreadid=512185 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting to the address
I'm not sure what you want to do? Do you want to know how many times your
maximum value is listed in a column? Let's say your data is in column A (for this example) Use this to find the # times the max is listed in column A. =COUNTIF(A:A,LARGE(A:A,1)) Just make sure you don't put it in column A or you'll have a circular reference. "keenasmustard" wrote in message news:keenasmustard.237v7e_1139916003.307@excelforu m-nospam.com... Have been using excel for some years now (self taught) and there are a few occasions where things drive me nuts. Usually I sort them out, but this one has beaten me before and has cropped up again to haunt me. It goes somthing like this. I have a column of results of which I can extract the highest result. No problems there. But I also wish to see if that result is repeated. Again no problem there, using "Large". I can locate the first result using "Match" to find the row,then add one to this to start the search again from the last result +1. Ok, still with me. So I have my new range, but do not know how to use it. Have looked at many, many web sites and tried nearly all the most likley functions in excel. All except the right one. HELP PLEASE. Keenasmustard -- keenasmustard ------------------------------------------------------------------------ keenasmustard's Profile: http://www.excelforum.com/member.php...o&userid=31524 View this thread: http://www.excelforum.com/showthread...hreadid=512185 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting to the address
you haven't really been clear about what your problem is - is it how to
define the second range, or how to find the maximum? from what you've said, to see if the maximum is repeated, just do a count on the maximum, e.g. =COUNTIF(A1:A23,MAX(A1:A23)) where your range is A1:A23. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting to the address
Assuming that the lookup range is A1:A100, and the first match row is stored
in C1, use =MATCH(MAX($A$1:$A$100),OFFSET($A$1,C1,0,100-C1,1),0)+C1 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keenasmustard" wrote in message news:keenasmustard.237v7e_1139916003.307@excelforu m-nospam.com... Have been using excel for some years now (self taught) and there are a few occasions where things drive me nuts. Usually I sort them out, but this one has beaten me before and has cropped up again to haunt me. It goes somthing like this. I have a column of results of which I can extract the highest result. No problems there. But I also wish to see if that result is repeated. Again no problem there, using "Large". I can locate the first result using "Match" to find the row,then add one to this to start the search again from the last result +1. Ok, still with me. So I have my new range, but do not know how to use it. Have looked at many, many web sites and tried nearly all the most likley functions in excel. All except the right one. HELP PLEASE. Keenasmustard -- keenasmustard ------------------------------------------------------------------------ keenasmustard's Profile: http://www.excelforum.com/member.php...o&userid=31524 View this thread: http://www.excelforum.com/showthread...hreadid=512185 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting to the address
Rob Hick Wrote: you haven't really been clear about what your problem is - is it how to define the second range, or how to find the maximum? from what you've said, to see if the maximum is repeated, just do a count on the maximum, e.g. =COUNTIF(A1:A23,MAX(A1:A23)) where your range is A1:A23. Rob Thanks for your reply. I have the "countif" part sorted. What I need to do is search my column for the position of the second occurance of the number. Have got to the part where I have the new range to search, but don't know how to get "Match" to pick up on the cell that has my range address listed. keenas -- keenasmustard ------------------------------------------------------------------------ keenasmustard's Profile: http://www.excelforum.com/member.php...o&userid=31524 View this thread: http://www.excelforum.com/showthread...hreadid=512185 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting to the address
Bob Phillips Wrote: Assuming that the lookup range is A1:A100, and the first match row is stored in C1, use =MATCH(MAX($A$1:$A$100),OFFSET($A$1,C1,0,100-C1,1),0)+C1 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "keenasmustard" wrote in message news:keenasmustard.237v7e_1139916003.307@excelforu m-nospam.com... Have been using excel for some years now (self taught) and there are a few occasions where things drive me nuts. Usually I sort them out, but this one has beaten me before and has cropped up again to haunt me. It goes somthing like this. I have a column of results of which I can extract the highest result. No problems there. But I also wish to see if that result is repeated. Again no problem there, using "Large". I can locate the first result using "Match" to find the row,then add one to this to start the search again from the last result +1. Ok, still with me. So I have my new range, but do not know how to use it. Have looked at many, many web sites and tried nearly all the most likley functions in excel. All except the right one. HELP PLEASE. Keenasmustard -- keenasmustard ------------------------------------------------------------------------ keenasmustard's Profile: http://www.excelforum.com/member.php...o&userid=31524 View this thread: http://www.excelforum.com/showthread...hreadid=512185 Bob Thank you so much. Its so simple when you see it Regards keenasmustard -- keenasmustard ------------------------------------------------------------------------ keenasmustard's Profile: http://www.excelforum.com/member.php...o&userid=31524 View this thread: http://www.excelforum.com/showthread...hreadid=512185 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Getting to the address
keenasmustard Wrote: Have been using excel for some years now (self taught) and there are a few occasions where things drive me nuts. Usually I sort them out, but this one has beaten me before and has cropped up again to haunt me. It goes somthing like this. I have a column of results of which I can extract the highest result. No problems there. But I also wish to see if that result is repeated. Again no problem there, using "Large". I can locate the first result using "Match" to find the row,then add one to this to start the search again from the last result +1. Ok, still with me. So I have my new range, but do not know how to use it. Have looked at many, many web sites and tried nearly all the most likley functions in excel. All except the right one. HELP PLEASE. Keenasmustard Thanks to those who offered help. I now have a working solution. Thanks Again Keenas -- keenasmustard ------------------------------------------------------------------------ keenasmustard's Profile: http://www.excelforum.com/member.php...o&userid=31524 View this thread: http://www.excelforum.com/showthread...hreadid=512185 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Merge address in XL to Word letter template | Excel Discussion (Misc queries) | |||
| how do I print address labels from an Excel Spreadsheet | Excel Worksheet Functions | |||
| Separate address column to Five columns | Excel Worksheet Functions | |||
| Excel email address hyperlink does not update | Excel Discussion (Misc queries) | |||
| How do I stop e-mail address from turning into link in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
