?
Solved

Find average of cell that are yellow in Excel spreadsheet range

Posted on 2011-04-26
13
Medium Priority
?
352 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.
0
Comment
Question by:fselliott
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35470250
You'll need code - is that OK for you?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35470252
Or a helper column, if you can't use code.
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1000 total points
ID: 35470271
You will need a UDF. Place this code in your VBA project:

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 Then
            Total = Total + Cell.Value
            Count = Count + 1
        End If
    Next Cell
   
    AverageYellow = Total / Count
   
End Function

Use it like this:

   =AverageYellow(A1:A100)

Kevin
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35470273
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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35470288
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35472826
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
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35472835
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35473931
Surely you need to count by colour too?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35476645
@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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35476684
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.
0
 

Author Comment

by:fselliott
ID: 35479089
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
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 1000 total points
ID: 35479112
Put the line:

if count > 0 then
  AverageYellow = Total / Count
else
  AverageYellow = 0
end if

I think its the same issue I mentioned with my solution - getting a Div by zero i the function is making it hang...

Dave
0
 

Author Closing Comment

by:fselliott
ID: 35484284
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
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question