3

I'm trying to apply a LARGE function to get the 5 largest values from a column ignoring duplicates. I did some searching around and found some proposed solutions but they don't seem to work for me. In my actual application the range is found by an array formula so I'm not sure if non array formulas will work here. My original function was {=LARGE(IF($A:$A=$A2,$B:$B),2)} dragged down the column. Replace 2 with 3 for the third largest, 4 for the fourth largest, etc. This worked except in the case of duplicates.

For example:

The data is in column 1 and the desired output is in column 2:

1    5 
2    4
2    3
3    2 
3    1
3
4
4
5

Things I have tried:

  1. {=LARGE(IF(A:A < LARGE(A:A,i-1),A:A),1)} for the i-th largest value.

    -Inputting this gives 3 for both i = 3 and i = 4.

  2. =LARGE(A:A,COUNTIF(A:A,MAX(A:A))+1)

    -This doesn't seem to be any different than just LARGE(A:A,2)

  3. {=MAX(IF(A:A < LOOKUP(9.99999999999999E+307,A:A),A:A))}

    -This does retrieve the value 4 but I'm not sure how to generalize it to the kth largest value.

VBA solutions are fine as well. I tried to code one myself but wasn't sure how to code in the array formula IF($A:$A=$A2,$B:$B).

1 Answer 1

2

In B1 enter:

=LARGE($A$1:$A$9,1)

In B2 enter the Array Formula:

=MAX(IF(A$1:A$9<B1,A$1:A$9))

and copy down. Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

enter image description here

Basically, we exclude previously found items from the Large() / Max() range.

3
  • Amazing! I had to use an array formula for the initial large function as well since my range was defined using an IF() but everything seems to be good. Took a while to compute but I suppose that comes with the array function territory. Might you have some insight into why the formulas I put in my original post failed for my usage case? Commented Oct 5, 2015 at 23:29
  • 1
    @learningAsIGo Your equations had the right idea, but did not cascade properly. Commented Oct 5, 2015 at 23:35
  • I'm not sure what you mean by cascade. Do you mean my array formulas would have to reference the previous value somehow instead of just copying down the column? Commented Oct 5, 2015 at 23:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .