3

So I have a column say A that looks like this:

I
15
0
3
15
M
8
0
8
21
Q
0
5
0
0

I can find the largest value with =MAX(A1:A100) I can find the smallest value with =SMALL(A1:A100,1)

How do i find the one before the largest and the one before that. I can use Large to call the Nth value, but then how do I know how many N values the function counts so I can know what positions it uses refer to the last 3 (largest and the 2 before)?

Ignoring duplicates - in the example the largest is 21, the one before is 15 and before that is 8. If I do =LARGE(A1:A100,3) it will return 15 as there is two of em in the column. And I'm looking for the 8 as the third largest value.

Below you will find the helpful folks of the Internet to have provided a PivotTable, an Array Formula solution, and I was finally able to do a simple formula one.

You can provide vba for the job should you insist we have everything, otherwise there is enough already.

1
  • @Raystafarian yeah verified a duplicate - this one here has 3 valid solutions (including the single solution at the dupe link).
    – helena4
    Dec 9, 2015 at 10:24

4 Answers 4

4

Use a PivotTable to quickly get a de-duped version of your list (just add your column to Row Labels), then use LARGE and SMALL functions on the de-duped list:

=LARGE(D4:D9,3)

PivotTable to de-dupe list

If you like, you can tidy up the PivotTable by removing the Grand Total and switching Field headers off so it just shows your de-duped list.

2
  • Yeah, I guess I'll stick with it should there be no formula way out. Though it might just become bulky if i have to set aside the service space for a table at every isolated space i need to do this. Plus the excel parser they use to extract my data references sheets in a dumb way.
    – helena4
    Dec 8, 2015 at 13:04
  • This PivotTable approach is great if you just need something quick or if you're dealing with just one table. If you have many tables and columns to deal with an array formula is likely better, as others have suggested. However array formulas bring their own problems - they can be slow to execute, in which case it may be time to look at how you can improve the way your data is coming into your workbook. But that's another question :)
    – Andi Mohr
    Dec 8, 2015 at 13:37
3

With data in column A like:

enter image description here

In B1 enter:

=MAX(A:A)

In B2 enter the array formula:

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

Then copy B2 down as far as you need.

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

enter image description here

1
  • Fairly nice, it can also be cropped to show a top 5, way quicker than pivots need. I'll see if the array doesn't mess up some of the numerous things i got going on it that document. Last time i did an array something made a mess, i don't remember if it was vba, interdocument link or smtn else. I'll keep the question open while i fiddle with it.
    – helena4
    Dec 8, 2015 at 13:27
2

With all the cool help I've been getting it made me think if i can do what the pivot table does and I came up this: (even on my own LOL)

=IF(COUNTIF($A$1:A1,A1)<=1,A1,"")

Making a parallel column to the data of interest solves it. (e.g paste in C1 and copy down)

It provides the initial de-duped list you can base large and small from. As a simple solution it shouldn't interfere with any volatile stuff you may have going on in your documents. And I for one can live without the vba. Any unexpected turns should be containable with IFERROR, should there be mixed data types.

-1

@helena4 Try this array formula

=LARGE(IF(A1:A15 < LARGE(A1:A15,1),A1:A15),3)

Then Press Ctrl+Shift+Enter

it will ignore the duplicates.

1
  • 3
    Your formula returns inconsistent results. It seems to be failing if there are triples or more. With data: {1;2;3;4;5;5;6;6;7;7;7;7;8;8;8} --> 7. {1;2;3;4;5;5;6;6;7;7;1;1;8;8;8} --> 6 Dec 8, 2015 at 12:51

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