Conditional Formatting Bottom 10 with "ties"

D

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
 
B

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
 
D

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
 
B

Biff

Ok.....

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

For the bottom 10:

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

For the top 10:

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

Biff
 
Top