Largest and smallest values, restricted

[Gimics]

Hello,

I need help evaluating a range for the largest and smallest values, but I would like to restrict my output based on another range.

Assume A1:A100 include values ranging from $100 - $100,000 and column B1:B100 includes percentages ranging from 0% - 50%.

I would like to identify the top five percentages in column B, but only for values in column A that exceed $20,000. This was my solution of removing the possible of outliers in my data.

I thought I could start with the =Large(B1:B100,1) to 5, but have no ideas for how to restrict the output based on the $20,000 threshold; thoughts?

 

[Michael M]

Hi and Welcome to the Forum
Have you considered simply using Custom Filters ?

Filter for the values in A first, then the top 5 in B

 

[Gimics]

Yes - definitely a consideration, but I am trying to build a report on one tab, and reference to a data table on the second tab. The data table is linked to a pivot table, which is linked to an OLAP cube and upated constantly. For automation purposes, I would rather have a formula that will update as the data table changes, instead of manually formatting every time.

 

[lexxie2013]

Have you tried using the array formula :

=LARGE(IF(A1:A100>20000,B1:B100),1)

Remember Ctrl-Shift-Enter as it's an array formula. Then you could replace the 1 with 2,3,4 and 5 to get the next largest.

 

[Gimics]

Perfect - the application of those array formulas always escapes me.

Thanks!!

 

[Michael M]

Adding to Lexxies formula, you could also replace the dollar value with a variable

=LARGE(IF(A1:A100>D1,B1:B100),1)

So it can then adjusted to suit given values