Getting to the address

K

keenasmustard

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
 
B

Barb Reinhardt

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" <[email protected]>
wrote in message
news:[email protected]...
 
R

Rob Hick

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.
 
B

Bob Phillips

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" <[email protected]>
wrote in message
news:[email protected]...
 
K

keenasmustard

Rob said:
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.
Click to expand...


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
 
K

keenasmustard

Bob said:
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"
<[email protected]>
wrote in message
Click to expand...



Bob

Thank you so much.
Its so simple when you see it

Regards
keenasmustard
 
K

keenasmustard

keenasmustard said:
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
Click to expand...


Thanks to those who offered help.
I now have a working solution.

Thanks Again

Keenas
 
Top