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

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

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.

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

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

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

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