Find average of cell that are yellow in Excel spreadsheet range

You'll need code - is that OK for you?

Or a helper column, if you can't use code.

To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

Kevin

The color I used is yellow. Here are some other codes for other shades of "yellow":

   Orange = 46
   LightOrange = 45
   Gold = 44
   Tan = 40
   DarkYellow = 12
   LightYellow = 36

Kevin

Effective.


Here's a more generic approach that you may find useful with less code tailoring in the future.  

A link to such is here:  http://www.excelexchange.com/SumByColor.html

use the function in that tip to sum or count a range of cells, by a given color....

Here's the code from that tip:

Function SumByColor(CellColor As Range, SumRange As Range)
  Application.Volatile
  Dim ICol As Integer
  Dim TCell As Range
  ICol = CellColor.Interior.ColorIndex
  For Each TCell In SumRange
    If ICol = TCell.Interior.ColorIndex Then
      SumByColor = SumByColor + TCell.Value
    End If
  Next TCell
End Function

Function CountByColor(CellColor As Range, CountRange As Range)
  Application.Volatile
  Dim ICol As Integer
  Dim TCell As Range
  ICol = CellColor.Interior.ColorIndex
  For Each TCell In CountRange
    If ICol = TCell.Interior.ColorIndex Then
      CountByColor = CountByColor + 1
    End If
  Next TCell
End Function

Select allOpen in new window

Now, with this pasted in a code module, you can use these two functions:

Function SumByColor(CellColor As Range, SumRange As Range)
and

Function CountByColor(CellColor As Range, CountRange As Range)

To achieve your average, you can put this in your formula:

=SumByColor(B1,B2:B100)/Count(B1:B100)

Note in this example, cell B1 is a cell you've colored so that you don't have to worry about color code numbers.  There are other implementations or even a modification of this one that take an actual color code.  I thought it would be nice to share a more generic and code independent version for diversity of answer.

FWIW, the attached file is the posted tip's example spreadsheet...

Cheers,

Dave
SumByColour.xls

Also, one could be a bit more graceful with the average.  if count(B1:B100) = 0, then you'll get the #DIV!0 result.

You could also code as follows:

=if(COUNT(B2:B100)=0,0,SumByColor(B1,B2:B100))

or just the original post - syntax corrected to:

=SumByColor(B1,B2:B100)/COUNT(B2:B100)

Dave

Surely you need to count by colour too?

@rorya - I had thought about that, and was unclear to the Asker's intent.

To clarify:

If you want to SUM the Color-coded cells, dividing by the count of all cells in the range, then use this formula:

=SumByColor(B1,B2:B100)/COUNT(B2:B100)

If you want to SUM the Color-coded cells, dividing by the count of the color-coded cells, then use this formula:

=SumByColor(B1,B2:B100)/CountByColor(B1,B2:B100)

Again, where B1 (in this example) is color coded the same way you're screening your data in range B2:B100

Dave

As the asker wants an average of yellow cells, it presumably has to be the latter option.

Either way, it would be a lot simpler and more reliable to use an additional column and enter say an 'x' value and then average based on that to avoid the recalc problems with using coloured cells as data.

I think I will only need to "count" the cells if there is a value in them, correct?  So far, I have come up with the following, but it seems to hang-up when the range in which it is looking for the color yellow, does not have any yellow cells:

Public Function AverageYellow(ByVal Source As Range)
    Dim Cell As Range
    Dim Total As Double
    Dim Count As Long
    Application.Volatile
    For Each Cell In Source
        If Cell.Interior.ColorIndex = 6 And Cell.Value <> 0 Then
            Total = Total + Cell.Value
            Count = Count + 1
        End If
    Next Cell
    AverageYellow = Total / Count
End Function

AND:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Worksheets("A1").Range("AV3").Value = AverageYellow(ActiveSheet.Range("I3:I500"))
    Worksheets("A1").Range("AW3").Value = AverageYellow(ActiveSheet.Range("S3:S500"))
    Worksheets("A1").Range("AX3").Value = AverageYellow(ActiveSheet.Range("X3:X500"))
    Worksheets("A1").Range("AY3").Value = AverageYellow(ActiveSheet.Range("AD3:AD500"))
    Worksheets("A1").Range("AZ3").Value = AverageYellow(ActiveSheet.Range("AI3:AI500"))
    Worksheets("A1").Range("BA3").Value = AverageYellow(ActiveSheet.Range("AQ3:AQ500"))
    Worksheets("A1").Range("BB3").Value = AverageYellow(ActiveSheet.Range("AS3:AS500"))
    Worksheets("A1").Range("AV4").Value = AverageRed(ActiveSheet.Range("C3:H500"))
    Worksheets("A1").Range("AW4").Value = AverageRed(ActiveSheet.Range("K3:R500"))
    Worksheets("A1").Range("AX4").Value = AverageRed(ActiveSheet.Range("U3:W500"))
    Worksheets("A1").Range("AY4").Value = AverageRed(ActiveSheet.Range("Z3:AC500"))
    Worksheets("A1").Range("AZ4").Value = AverageRed(ActiveSheet.Range("AF3:AH500"))
    Worksheets("A1").Range("BA4").Value = AverageRed(ActiveSheet.Range("AK3:AP500"))
    Worksheets("A1").Range("BB4").Value = AverageRed(ActiveSheet.Range("AS3:AS500"))

END SUB

Thank you both. As there were many sheets, with different variables, I used a comblination of both solutions to come up with the following (I'm only including two snippets of the code as it is overall quite a comblination of code and variables):

Public Function AverageYellow(ByVal Source As Range)
    Dim Cell As Range
    Dim Total As Double
    Dim Count As Long
    Application.Volatile
    For Each Cell In Source
        If Cell.Interior.ColorIndex = 6 And Cell.Value > 0 Then
            Total = Total + Cell.Value
            Count = Count + 1
        End If
    Next Cell
         If Count > 0 Then
             AverageYellow = Total / Count
                Else
             AverageYellow = 0
        End If
End Function

Public Function AverageRed(ByVal Source As Range)
    Dim Cell As Range
    Dim Total As Double
    Dim Count As Long
    Application.Volatile
    For Each Cell In Source
        If Cell.Interior.ColorIndex = 3 And Cell.Value > 0 Then
            Total = Total + Cell.Value
            Count = Count + 1
                Else
            Total = Total + 0
            Count = Count + 0
        End If
    Next Cell
         If Count > 0 Then
             AverageRed = Total / Count
                Else
             AverageRed = 0
        End If
End Function

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Worksheets("BH").Range("AV3").Value = AverageYellow(Sheets("BH").Range("I3:I500"))
    Worksheets("BH").Range("AW3").Value = AverageYellow(Sheets("BH").Range("S3:S500"))
    Worksheets("BH").Range("AX3").Value = AverageYellow(Sheets("BH").Range("X3:X500"))
    Worksheets("BH").Range("AY3").Value = AverageYellow(Sheets("BH").Range("AD3:AD500"))
    Worksheets("BH").Range("AZ3").Value = AverageYellow(Sheets("BH").Range("AI3:AI500"))
    Worksheets("BH").Range("BA3").Value = AverageYellow(Sheets("BH").Range("AQ3:AQ500"))
    Worksheets("BH").Range("BB3").Value = AverageYellow(Sheets("BH").Range("AS3:AS500"))
    Worksheets("BH").Range("AV4").Value = AverageRed(Sheets("BH").Range("C3:H500"))
    Worksheets("BH").Range("AW4").Value = AverageRed(Sheets("BH").Range("K3:R500"))
    Worksheets("BH").Range("AX4").Value = AverageRed(Sheets("BH").Range("U3:W500"))
    Worksheets("BH").Range("AY4").Value = AverageRed(Sheets("BH").Range("Z3:AC500"))
    Worksheets("BH").Range("AZ4").Value = AverageRed(Sheets("BH").Range("AF3:AH500"))
    Worksheets("BH").Range("BA4").Value = AverageRed(Sheets("BH").Range("AK3:AP500"))
    Worksheets("BH").Range("BB4").Value = AverageRed(Sheets("BH").Range("AS3:AS500"))

    Worksheets("A1").Range("AV3").Value = AverageYellow(Sheets("A1").Range("I3:I500"))
    Worksheets("A1").Range("AW3").Value = AverageYellow(Sheets("A1").Range("S3:S500"))
    Worksheets("A1").Range("AX3").Value = AverageYellow(Sheets("A1").Range("X3:X500"))
    Worksheets("A1").Range("AY3").Value = AverageYellow(Sheets("A1").Range("AD3:AD500"))
    Worksheets("A1").Range("AZ3").Value = AverageYellow(Sheets("A1").Range("AI3:AI500"))
    Worksheets("A1").Range("BA3").Value = AverageYellow(Sheets("A1").Range("AQ3:AQ500"))
    Worksheets("A1").Range("BB3").Value = AverageYellow(Sheets("A1").Range("AS3:AS500"))
    Worksheets("A1").Range("AV4").Value = AverageRed(Sheets("A1").Range("C3:H500"))
    Worksheets("A1").Range("AW4").Value = AverageRed(Sheets("A1").Range("K3:R500"))
    Worksheets("A1").Range("AX4").Value = AverageRed(Sheets("A1").Range("U3:W500"))
    Worksheets("A1").Range("AY4").Value = AverageRed(Sheets("A1").Range("Z3:AC500"))
    Worksheets("A1").Range("AZ4").Value = AverageRed(Sheets("A1").Range("AF3:AH500"))
    Worksheets("A1").Range("BA4").Value = AverageRed(Sheets("A1").Range("AK3:AP500"))
    Worksheets("A1").Range("BB4").Value = AverageRed(Sheets("A1").Range("AS3:AS500"))

END SUB