Calculate min, max and average of a range of cells, but only of coloured cells

[Danikuku]

Hi Guys,

I need to calculate min, max and average of a range of cells (ex. A2:C6).
This appears to be easy, except in this range of cells, I have some cells with different background colours and what I want is to calculate this min, max and average of all cells that have a background colour (independently of the colour).

In another words, I want the formula to ignore all cells with no background colour and consider only the coloured ones.

Thanks!

 

[Mike LH]

Hi,

I have assumed that these are fill colours and NOT conditional formatting. ALT+F11 to open VB editor, right click ThisWorkbook and insert module and paste the code below in on the right. Close vb editor.

Back on the worksheet call with this in a cell


=CountColour(A1:C6,n)

Where n =

1 for minimum
2 for maximum
3 for average

Function CountColour(rng As Range, TheType As Long) As Double
Dim MyRange As Range
Application.Volatile
Dim c As Range
For Each c In rng
    If c.Interior.Color <> 16777215 Then
       If MyRange Is Nothing Then
                Set MyRange = c
        Else
                Set MyRange = Union(MyRange, c)
        End If
    End If
Next
Select Case TheType
    Case Is = 1
        CountColour = WorksheetFunction.Min(MyRange)
    Case Is = 2
        CountColour = WorksheetFunction.Max(MyRange)
    Case Is = 3
        CountColour = WorksheetFunction.Average(MyRange)
    End Select
End Function

 

[Danikuku]

Thanks a lot Mike! It worked just fine!