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" wrote in message
ups.com...
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 FormatConditional 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" wrote in message
oups.com...
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" wrote in message
ups.com...
Very cool! Thanks much for your help Biff!