Conditional Formatting Bottom 10 with "ties"

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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 -
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

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

"Dan" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Very cool! Thanks much for your help Biff!

You're welcome. Thanks for the feedback!

Biff

"Dan" <[email protected]> wrote in message
news:[email protected]...
> Very cool! Thanks much for your help Biff!
>