Conditional Formatting Bottom 10 with "ties"

[Dan]

Hello all -
I am wanting to highlight the Bottom 10 cells in a range ($D$6:$D$25),
I've got the basic formula but am confused when it comes to cells with
"ties" (a.k.a. the same number), as it wants to highlight more than 10
cells at times.

My formula is:
=D6<=SMALL($D$6:$D$25,10)

How can I modify this so it will account for a "tie" with another cell?

Thanks!
Dan

 

[Biff]

Hi!

Not sure what you're asking.

If a bottom n (or top n) list contains ties, those ties should be factored
in the n criteria.

A bottom 10 list can be more than 10 values if each value isn't unique It
could even be the entire range depending on the ties.

There was a post in another forum last night that wanted the top 5. I asked
this question (I'll adapt it to your situation):

Suppose you want the bottom 3. (assuming the low values are considered the
bottom)

These are the values:

1;1;1;1;2;2;3;4;5;6;7

.......................................1..1..1..1..2..2..3..4..5..6..7
Would the bottom 3 be: ..1..2..3

.......................................1..1..1..1..2..2..3..4..5..6..7
Would the bottom 3 be: ..1..2..3..3

.......................................1..1..1..1..2..2..3..4..5..6..7
Would the bottom 3 be: ..1..1..1..1..2..2..3

Biff

 

[Dan]

Biff -
Thanks for your response. In my situation, I would say the bottom
three are 1..1..1. Even though there are 4 in your example, I would
just want the first three.

Thanks,
Dan

 

[Biff]

Ok.....

Select the range D625
Goto Format>Conditional Formatting
Formula Is: use the one that applies

For the bottom 10:

=RANK(D6,D$6$25,1)+COUNTIF(D$66,D6)-1<=10

For the top 10:

=RANK(D6,D$6$25)+COUNTIF(D$66,D6)-1<=10

Biff

 

[Dan]

Very cool! Thanks much for your help Biff!

 

[Biff]

You're welcome. Thanks for the feedback!

Biff