conditional formatting

[RTE]

I conditionally formatted 10 performance indicators for several institutions to identify the top 20% performers. How do I count the number of times an institution is in the top 20% (count conditionally formatted cells for institution)? Had to use a formula in the conditional formatting dialog box.

About out of options. Thanks.

 

[jeffreybrown]

Maybe this is an option.

Function COUNTCOLOR(rColor As Range, rCountRange As Range)
    Dim rCell  As Range
    Dim iCol   As Integer
    Dim Result

    Application.Volatile

    iCol = rColor.Interior.ColorIndex

    For Each rCell In rCountRange
        If rCell.Interior.ColorIndex = iCol Then
            Result = Result + 1
        End If
    Next rCell
    '=COUNTCOLOR(A1,BZ18:CZ29)
    'Where A1 houses the colour of choice (e.g. yellow)
    COUNTCOLOR = Result
End Function

And you could also look at
http://www.cpearson.com/excel/colors.aspx

 

[taigovinda]

I'm presuming your data range has names in column A and scores in column B.

You can set up a formula to identify a threshhold for top 20% (e.g., 80 is the minimum to be in the top 20%) that would be something like:

=large($B$1:$B$100,count($B$1:$B$100)/5)

Then use the result of that to do your counting.

So, something like:

=sumproduct(--($A$1:$A$100=A1),--($B$1:$B$100>large($B$1:$B$100,count($B$1:$B$100)/5)))

...or if the large() formula is in cell C1 for instance, then:

=sumproduct(--($A$1:$A$100=A1),--($B$1:$B$100>$C$1))

Hope that helps - if not maybe show us a sample of your data.

Tai

 

[taigovinda]

Jeff,

Also from Mr Pearson's website ( http://www.cpearson.com/excel/cfcolors.htm ):

"Unfortunately, the Color and ColorIndex properties of a Range don't return the color of a cell that is displayed if Conditional formatting is applied to the cell. Nor does it allow you to determine whether a conditional format is currently in effect for a cell. "

He does have some code on there for determining what colors are active in conditionally formatted cells, but I think it is much more complex than what you have in post 2...

Tai

 

[jasonb75]

Jeff, I could be wrong but thought colorcount functions only work with fill color, not conditional formats.

there is a good suggestion here.

http://www.mrexcel.com/forum/showthread.php?t=544005