Syntax for LARGE with criteria using OR

[Phillycheese5]

Hi,
I have column M with security type and column N with percentages. My goal is to get the largest, second-largest, etc. percentage if the security type is Equity or Fixed. I tried a lot of variations but cannot get it to work. Here's one of the things I tried:

=LARGE(IF($M$30:$M$500=OR("Equity","Fixed")),$N$30:$N$500),1)

Any help would be appreciated.
Thanks,
Phillycheese

[TMS]

Please Login or Register  to view this content.

Array Entered with Ctrl-Shift -Enter rather than just Enter.


Regards, TMS

[FDibbins]

Try this ARRAY, adjusted for your ranges...
=LARGE(IF($B$1:$B$18={"equity","fixed"},$A$1:$A$18,0),ROW(A1))

edit: Is there an echo in here, Trevor?

[Ron Coderre]

This regular formula returns the nth largest value where the corresponding type is Fixed or Equity

Please Login or Register  to view this content.

Replace the 1 with whichever largest value you want the formula to return.

Is that something you can work with?

[TMS]

Slight amendment for Ron's solution:

Please Login or Register  to view this content.

Regards, TMS

[Phillycheese5]

These are great solutions!!! Thank you Ron and TMS!!

[Ron Coderre]

Glad you got something you can use!

...and thanks for the feedback. Much appreciated.

[TMS]

@Phillycheese5: You're welcome. Thanks for the rep.

@Ford: the IFERROR had faded in your post

@Ron: I prefer your solution as I prefer to avoid array solutions ... too easy to forget to press all the right keys.

Regards all, TMS

[Tony Valko]

Here's another one if you're using Excel 2010 or later...

=AGGREGATE(14,6,$N$30:$N$500/($M$30:$M$500={"Equity","Fixed"}),1)