Problems finding the Nth biggest
Juan
How can i do if i want to find the Nth biggest and this value is the
same that of the N+1?
In my current implementation in this case i lost the N+1.
Thank you.
Steven Ung
Juan <j...@hotmail.com> wrote in message
news:38BB33D5...@hotmail.com...
Juan
A
1 11
2 15
3 500
4 50
5 549
6 =MAX(IF(A1:A5>0, A1:A5, 0))
Note in A6, the formula is an array formula. You have to press
CTRL+SHIFT+ENTER instead of just ENTER.
HTH
Tom Ogilvy
David McRitchie
Do not fully understand your question. If you have numbers like
22,3,5,4,22,33
then the 2nd and the 3rd largest are 22 are you trying to identify
data with them or what.
Based on a newsgroup reply by George Simms 2000-01-30,
so you can use the fill-handle.
=LARGE($A$1:$A$13,ROW(1:1))
=LARGE($A$1:$A$13,ROW(2:2))
=LARGE($A$1:$A$13,ROW(3:3))
=LARGE($A$1:$A$13,ROW(4:4))
=LARGE($A$1:$A$13,ROW(5:5))
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm
Juan <j...@hotmail.com> wrote
Juan
larger , but if both have the same value I loose the 6th
A B
1 d 4
2 e 3
3 f 3
If this is my range if i seek the value in column a for the n-bigger
number in colum B
i get the next
n=1 => d right
n=2 => e right
n=3 => e wrong
Tom Ogilvy wrote:
> Look at the large function.
>
> Regards,
> Tom Ogilvy
>
> Juan wrote in message <38BB33D5...@hotmail.com>...
> >Hi All.
> >How can i do if i want to find the Nth biggest and this value is the
> >same that of the N+1?
> >In my current implementation in this case i lost the N+1.
> >
> >Thank you.
> >
Denny Campbell
Assuming whole numbers and using your example. Copy the array formula:
=OFFSET($A$1,MATCH(LARGE(($B$1:$B$3 - ROW($B$1:$B$3)/10^5) , ROW(A2)) ,
($B$1:$B$3-ROW($B$1:$B$3)/10^5),0)-1,0)
to C1. Because it's an array formula you'll have to enter it with
Ctrl+Shift+Enter. Then copy the formula select cells B2:B3 and paste.
In the formula "ROW(A1)" = n
Basically the formula eliminates duplicates by subtracting from each number,
its row number "-ROW($B$1:$B$3)", as a fraction "/10^5". Then finds
LARGE(n). Finally it uses MATCH and OFFSET to get the value in column A.
HTH
--
Denny Campbell
Grand Rapids, Michigan
"Juan" <j...@hotmail.com> wrote in message
news:38BC821B...@hotmail.com...
Valère Mangelschots
A B C
1) 10 4 3
2) 8
3) 8
4) 4
5) 2
6) 4
7) 3
in cel C1 I can choose
in cel B1
{=LARGE((COUNTIF(OFFSET($A$1,0,0,ROW(A1:A7),1),A1:A7)=1)*A1:A7,C1)}
CTRL+SHIFT+ENTER
> How can i do if i want to find the Nth biggest and this value is the
> same that of the N+1?
> In my current implementation in this case i lost the N+1.
Groeten from Belgium