Reference to the N-largest value in an array

[michaelp]

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

 

[Ragdyer]

Try this *array* formula:

=ADDRESS(MAX((A1:B5=LARGE(A1:B5,2))*ROW(A1:B5)),MAX((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)),MAX((A1:B5=LARGE(A1:B5,2))*C
OLUMN(A1:B5)))

 

[michaelp]

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

 

[RagDyeR]

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!
=====================================================

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

 

[michaelp]

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