We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

# Find average of cell that are yellow in Excel spreadsheet range

on
Medium Priority
396 Views
How do I write a formula that will find the average of cells on a spreadsheet that are in column B and the cells are colored yellow?

The yellow cells are not conditionally formatted as they are not based on a condition, they are randomly chosen.
Comment
Watch Question

## View Solutions Only

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
You'll need code - is that OK for you?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Or a helper column, if you can't use code.
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
CERTIFIED EXPERT
Top Expert 2008

Commented:
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
CERTIFIED EXPERT
Top Expert 2008

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
``````

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)

=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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Surely you need to count by colour too?
Most Valuable Expert 2012
Top Expert 2012

Commented:

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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
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.

Commented:
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("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
Most Valuable Expert 2012
Top Expert 2012
Commented:
Unlock this solution with a free trial preview.
(No credit card required)

Commented:
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("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("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
Unlock the solution to this question.