Conditional formating for Top Ten values, avoiding duplicates

[Doug Suerich]

I have a list of 20 numbers, stored in the range A1:A20

I would like to apply a special format to the 10 largest numbers in
the list, but _exactly_ 10 numbers. So if there are two numbers tied
for the 10th largest, only one will receive the special formatting.
I'm indifferent as to which of the two is formatted.

Except for the duplicates problem, I can do this easily by setting a
conditional format for when the cell value is >= LARGE($A$1:$A$20,10).
By tweaking that slightly, I can either get all values equal to the
tenth largest to highlight, or none of them. I've been unable to get
just one highlighted.

Any ideas? Does anyone know if you can insert an array formula into
the conditional format? I've tried, but it doesn't appear to work.

Thanks in advance,
Doug

 

[Jonathan Rynd]

(e-mail address removed) (Doug Suerich) wrote in

Except for the duplicates problem, I can do this easily by setting a
conditional format for when the cell value is >= LARGE($A$1:$A$20,10).
By tweaking that slightly, I can either get all values equal to the
tenth largest to highlight, or none of them. I've been unable to get
just one highlighted.

Any ideas? Does anyone know if you can insert an array formula into
the conditional format? I've tried, but it doesn't appear to work.

Select the cells from A1 to A20. Be sure to click on A1 first. Then do
format/conditional format:

Cell value is, greater than or equal to,
=LARGE($A$1:$A$20+ROW($A$1:$A$20)/10000,10)-ROW(A1)/10000

works for me, at least when I have all integers.

As a bonus it works when there are 5 numbers having the same value as
LARGE(A1:A20) but you only need 2 of them.

The key is to break the symmetry between them, without affecting the
order. If you have numbers that differ by less than 20/10000, this won't
work.