Large Function with Repeated Values

=large(a1:a9, countif(a1:a9, max(a1:a9)) + 1)

If you have numbers in A1:A100 you can get a list of unique numbers in descending order like this:

In C1

=MAX(A1:A100)

Then in C2

=MAX(IF(ISNA(MATCH(A1:A100,C$1:C1,0)),A1:A100))

confirmed with CTRL+SHIFT+ENTER and copied down the column........or do you want a single formula to find the "nth" largest value (ignoring duplicates)?

I doubt if "array formulas" are going away any time soon. There's still no MAXIF function (so you can't do the above without CSE, even in Excel 2010) and there are many other circumstances where you might need to use one.

Of course AVERAGEIFS function eliminates some need and AGGREGATE function in 2010 lets you sum and average (and other things) while ignoring errors so that will obviate the need for another category of CSE....

....but I reckon I suggest at least one a day, probably more, here or somewhere else.....

For 3rd largest value you could extend the logic of shg's suggestion, i.e.

=LARGE(A1:A100,COUNTIF(A1:A100,">="&LARGE(A1:A100, COUNTIF(A1:A100, MAX(A1:A100)) + 1))+1)

assuming data in A1:A100

but that formula will then get progressively larger for 4th, 5th largest etc.

One way to get the nth largest value would be like this (array entered)

=LARGE(IF(COUNTIF(A$1:A$100,ROW(INDIRECT(MIN(A$1:A$100)&":"&MAX(A$1:A$100)))),ROW(INDIRECT(MIN(A$1:A$100)&":"&MAX(A$1:A$100)))),C1)

where C1 contains n

assumes that A1:A100 contains positive integers < 65536

Marvin,

Your suggestion sounds like a great idea to me. It's often more efficient to use helper columns or other techniques, especially so in this case where a single cell formula can get quite complex (and very inefficient).

In general I'm not sure that the users here are against intermediate steps. Common sense dictates that one should use the most appropriate method in each scenario, whether that be pivot tables, VBA, single cell formulas or helper columns.....but of course some users might be biased in favour of one particular approach, often the one they understand the best.

Of course I'm a connoisseur and purveyor of "complex" formulas......so my suggestions tend to be biased in that direction......but I know it's not always the best way.

regards, daddylonglegs

In another vain attempt to understand formula better, I cobbled this together using helper columns.

In B2 (Helper Column)

Please Login or Register  to view this content.

Drag/Fill Down

In C2 (Helper Column)

Please Login or Register  to view this content.

Drag/Fill Down

In the demo workbook enter your required ranking in E2 the result is given in D2 and highlighted in the table.
The helper columns can be toggled with the +/- grouping button(s).

Hope this helps

[EDIT]
Data must be continuous, i.e no blanks, zeros are okay.

I often build a formula in stages to check for potential errors, a bit like in maths problems (Let D2 = LARGE(A:A,ROW()-1)).
I'm just a simple engineer, no formula wizard!

Often this method points to a better way once all the factors are laid out in front of you.

By subsituting the Vaules D1 & D2 in Column E

in Row 2

Please Login or Register  to view this content.

will return #NUM! in Row 2 thereafter it is okay.

This is because Row()-2 = 0 in row 2
to correct this

Please Login or Register  to view this content.

Because I chose a slightly different approach, your formula extended is shorter.

Please Login or Register  to view this content.

This will cut out one helper column at least!

This is not meant to contradict the people who CAN BUILD supercharged formulae, just a very basic approach to a problem.

There are many ways to solve problems.

It's a bit like the old Scots adage
"There are no bad whiskies, just some are better than others.".....

On that note, a beer beacons!... ...

Slainte
Alistair

Hi,

I have a list of six loans and some contain duplicate amounts. Column A is the Loan Name and Column B is the amount of the loan:

Loan XYZ, $300
Loan ABC, $300
Loan MNO, $200
Loan PQR, $250
Loan HIJ, $250
Loan 123, $100

How would I display the top five loans with its corresponding loan amount, regardless of the duplicate values, with an array formula? Would I have to assign, in Column C, a unique value for each row?

I used the following formulas to pull the loan amounts:

=LARGE($B$1:$B$6,COUNTIF($B$1:$B$6,MAX($B$1:$B$6))+0)
=LARGE($B$1:$B$6,COUNTIF($B$1:$B$6,MAX($B$1:$B$6))+1)
=LARGE($B$1:$B$6,COUNTIF($B$1:$B$6,MAX($B$1:$B$6))+2)
=LARGE($B$1:$B$6,COUNTIF($B$1:$B$6,MAX($B$1:$B$6))+3)
=LARGE($B$1:$B$6,COUNTIF($B$1:$B$6,MAX($B$1:$B$6))+4)

I used the following formula to pull the loan name:

=INDEX($A$1:$A$6,MATCH([Dollar Amount of Loan],$B$1:$B$6,0))

The result I keep getting, which I don't want, is this:

Loan XYZ, $300
Loan XYZ, $300
Loan PQR, $250
Loan PQR, $250
Loan MNO, $200

My intended result would look like this:

Loan XYZ, $300
Loan ABC, $300
Loan PQR, $250
Loan HIJ, $250
Loan MNO, $200

I hope this is clear and apologize if it's confusing. Any help would be so appreciated.


Thanks,

Welcome to the forum Triv. You can create your own thread.

When you click "Excel General" subforum you'll notice the---> " + POST NEW THREAD" button...put the above and name it that is appropriate to your needs.




Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

@ triv, if possible, can you post your query in the new thread, then also put the solution you've got from Tony so others would also benefit from the solution provided to you.
you can then put a link to the new one in your post above so Tony and other members could follow the thread. And if other problems appear you can continue the discussions there.

Regards,
Vladimir