add criteria to an Index formula, <>100%

[gtd526]

Hello,
Im trying to add another criteria to the formula below, the additional criteria is DN6:DN35 <>.1
This formula creates a list of cell values meeting the existing criteria (>=.75). Fill down to create the list as needed.
thank you

=IFERROR(INDEX($DK$6:$DK$35, SMALL(IF($DN$6:$DN$35>=0.75, ROW($DN$6:$DN$35)-ROW($DN$6)+1), ROW(1:1))),"")

 

[DanteAmor]

Try:

=IFERROR(INDEX($DK$6:$DK$35, SMALL(IF(($DN$6:$DN$35>=0.75)*($DN$6:$DN$35<>.1), ROW($DN$6:$DN$35)-ROW($DN$6)+1), ROW(1:1))),"")

 

[Joe4]

Your thread title and original question are a bit confusing, as they seem to contradict each other.

Note how your thread title says "<>100%".
But your original question says "<>.1"

Note that these two things are NOT equivalent.
100% is actually equal to 1
.1 is equivalent to 10%
If you wanted 1%, that is equivalent to .01

So based on what you really intended, you may need to adjust the number in the formula to get the real value you want.

Note that if it really is .1, then the additional condition is totally unnecessary, as you are already checking to see if the number is greater than or equal to 0.75, and .1 is already less than that.
If it really is 100% (or 1), then you want:

=IFERROR(INDEX($DK$6:$DK$35, SMALL(IF(($DN$6:$DN$35>=0.75)*($DN$6:$DN$35<>1), ROW($DN$6:$DN$35)-ROW($DN$6)+1), ROW(1:1))),"")

 

[gtd526]

Your thread title and original question are a bit confusing, as they seem to contradict each other.

Note how your thread title says "<>100%".
But your original question says "<>.1"

Note that these two things are NOT equivalent.
100% is actually equal to 1
.1 is equivalent to 10%
If you wanted 1%, that is equivalent to .01

So based on what you really intended, you may need to adjust the number in the formula to get the real value you want.

Note that if it really is .1, then the additional condition is totally unnecessary, as you are already checking to see if the number is greater than or equal to 0.75, and .1 is already less than that.
If it really is 100% (or 1), then you want:

=IFERROR(INDEX($DK$6:$DK$35, SMALL(IF(($DN$6:$DN$35>=0.75)*($DN$6:$DN$35<>1), ROW($DN$6:$DN$35)-ROW($DN$6)+1), ROW(1:1))),"")

Yes, I wanted 100%, my mistake. Your formula is correct and works. Thank you.

 

[Joe4]

You are welcome.
Glad we were able to help!

Note that my formula was really just Dante's formula with the corrected numeric value.

 

[DanteAmor]

Note how your thread title says "<>100%".
But your original question says "<>.1"

Good catch