Reference to the N-largest value in an array
 

Hello

I need a formula that can give me the the array-reference of the N-
largest value in the array.

Something like:
referenceToLarge(A1:B5, 2) that gives me "A3", if A3 holds the second
largest value in the range A1:B5.



Thanks

Michael

Reference to the N-largest value in an array
 

Try this *array* formula:

=ADDRESS(MAX((A1:B5=LARGE(A1:B5,2))*ROW(A1:B5)),MA X((A1:B5=LARGE(A1:B5,2))*C
OLUMN(A1:B5)),4)

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

This returns a relative reference.

The last comma and 4 ( ,4 ) can be eliminated if you want an absolute
reference:

=ADDRESS(MAX((A1:B5=LARGE(A1:B5,2))*ROW(A1:B5)),MA X((A1:B5=LARGE(A1:B5,2))*C
OLUMN(A1:B5)))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
wrote in message
oups.com...
Hello

I need a formula that can give me the the array-reference of the N-
largest value in the array.

Something like:
referenceToLarge(A1:B5, 2) that gives me "A3", if A3 holds the second
largest value in the range A1:B5.



Thanks

Michael

Reference to the N-largest value in an array
 

Thanks. This works.

I am now trying to further develop this formula, i.e. use the value
that I get in a condition.
For this, I need to extract the row of the reference
=ROW(your formula)

But this seems impossible, probably because I put an array formula
where a scalar value belongs.

I tried both CSR, and normal entry, but to no avail. i get an error.

Is there a workaround?

Thanks again

Reference to the N-largest value in an array
 

You say you *only* want the row number, not the entire address?

You should have asked for what you exactly want at the outset.

This much shorter *array* formula will return the row number:

=MAX((A1:B5=LARGE(A1:B5,2))*ROW(A1:B5))

Don't forget the CSE entry!
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

wrote in message
ups.com...
Thanks. This works.

I am now trying to further develop this formula, i.e. use the value
that I get in a condition.
For this, I need to extract the row of the reference
=ROW(your formula)

But this seems impossible, probably because I put an array formula
where a scalar value belongs.

I tried both CSR, and normal entry, but to no avail. i get an error.

Is there a workaround?

Thanks again

Reference to the N-largest value in an array
 

On Aug 30, 4:53 pm, "RagDyeR" wrote:
You say you *only* want the row number, not the entire address?
Thank you very much!


You say you *only* want the row number, not the entire address?

I found out that I needed both. but in hindsight It would suffice
asking
about the former.

You should have asked for what you exactly want at the outset.

May be so. I think I learned much more by trying to generalize the
question, which I am trying to
keep as a policy when addressing news groups. This is also meant so
the forum -
as a source of information for others - benefits.

I regret if it, this time, caused unnecessary work. It mostly does
not.

Thanks again

Michael