Populating a list based on two conditions.

[Halabis]

I am attempting to generate a ranked list of values based on two conditions; percent and count. The end goal is for me to be able to produce a graph whose values can be changed dynamically by altering two fields.


The source data has a list of values, each one with a count (Column H) and a percent (Column K).

Field 1 (B2) measures minimum percent
Field 2 (B3) measures minimum count

A8 to A32 list the numbers 1 to 25, to give me the top 25 values


I have determined how to calculate the values I need using one variable.

{=LARGE(IF(K:K>=$B$1,H:H),A8)} This is in Cell B8, and I drag it down to B32 to match the 1-25's in cell A.

This formula works equally well with either Column H or K to give me the top 25 values based on percent or count.

I need to be able to filter on both or once. (Example: Top 75% with a count greater than 100)

I had thought the following might work, but it doesnt seem to return what I need.

=LARGE((K:K>=$B$1)*(H:H>=$B$2)*(H:H),A8)



Any ideas?

Thanks!

 

[Halabis]

I've also tried

{=LARGE(IF(AND(K:K>=$B$1,H:H>=$B$2),H:H),A8)}

which seems like it should work, but unless both B1 & B2 are 0, returns a 0. If they are both 0 then it returns the correct value.