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

fselliott
fselliott asked
on
Medium Priority
396 Views
Last Modified: 2012-08-14
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

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)
Get Preview
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

Open 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
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:
@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
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.

Author

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

Author

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("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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.