conditional format

[dave wagner]

I have a group of cells that counts the number of times a given value is
repeated, for example [=countif(A:F,"5J234b")] . In turn there are currently
73 row by 7 column of data. I have tried to get the "final count" column to
hight light the top 10 values. My problem is that I cannot seem to get the
conditional format to work correctly, the values are correct. What happens
is that all the cell values gets the same color. I was using the "max"
function for the conditional format.

I would appreicate some input on how to get the cell colored correctly, for
example the largest would be blue, next green, next orange, then yellow and
finally red.

Thanks ahead for any input.

 

[Max]

....

My problem is that I cannot seem to get the
conditional format to work correctly, the values are correct.
What happens is that all the cell values gets the same color.
I was using the "max" function for the conditional format.
I would appreicate some input
on how to get the cell colored correctly, for example the largest
would be blue, next green, next orange, then yellow and
finally red.

CF could be used to highlight only up to the Top 3 numbers

Try this simple experiment:

Assume a 3 x 3 grid, A1:C3

Put in A1: =RAND()
Copy across to C1, fill down to C3

With A1:C3 selected,

Click Format > Conditional Formatting

Condition 1, Formula: =A1=LARGE($A$1:$C$3,1)
Click Format button > Patterns tab > Red > OK

Repeat similarly for Conditions 2 and 3

Condition 2, Formula: =A1=LARGE($A$1:$C$3,2)
Format button > Patterns tab > Amber > OK

Condition 3, Formula: =A1=LARGE($A$1:$C$3,3)
Format button > Patterns tab > Green > OK

Click OK at the main dialog

The Top 3 random values within A1:C3 should appear highlighted as per the CF
fill colors set. Press F9 to re-generate a fresh set of random values, and
re-check ..

----------

.. hight light the top 10 values.

To get more CF colors than the top 3, you may wish to check out:

a. Bob Phillips' CFPlus - Extended Conditional Formatter add-in at:
http://www.xldynamic.com/source/xld.CFPlus.Download.html
(Bob's write-up there states that up to *30* cond formats are catered for
...)

b. JE McGimpsey's page at:
http://www.mcgimpsey.com/excel/conditional6.html
[Getting 6 conditional font colors without macros]
The page also contains links to Dave McRitchie's & Chip Pearson's CF pages

 

[Max]

Here's a link to a sample file with the implemented example:
http://savefile.com/files/1992487
File: ConditionalFormat_Highlight_Top3.xls

 

[dave wagner]

Thanks Max, got it to work.

 

[Max]

Glad to hear that, Dave !