# Excel question - resolved

Registered User regular
edited October 2015
I'm working on a project for work. I want to be able to find the average for a range of data based on the fill color of the cell. So I can have one formula to show the average of all the purple cells, another for the average of all the green cells, etc. I found a thing that works for finding the sum, but I don't know enough to change it to find the average. The code (?) for the sum is below. Any help would be appreciated!
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

JimBobtheMonkey on

## Posts

• Registered User regular
edited October 2015
Well, the average is just the sum divided by the number of cells. So in addition to calculating the sum, if you have an extra variable that's just a counter that starts at 0 and only increments inside the If block, then at the end you can divide the sum by that counter to get the average.

My VBA is rusty, but based on the code you have, something like:
Function AvgByColor(CellColor As Range, SumRange As Range)
Application.Volatile
Dim ICol As Integer
Dim TCell As Range
Dim ICounter as Integer
Dim SumByColor as Single
ICounter = 0
SumByColor = 0
ICol = CellColor.Interior.ColorIndex
For Each TCell In SumRange
If ICol = TCell.Interior.ColorIndex Then
SumByColor = SumByColor + TCell.Value
ICounter = ICounter + 1
End If
Next TCell
AvgByColor = SumByColor / ICounter
End Function

Not sure how picky VBA is about math operations on different datatypes (i.e. dividing a single by an int at the end).

Daenris on
• Registered User regular
edited October 2015
That gives a result rather than the errors I kept getting, but it's not calculating correctly. At least that gives me something to work with so I can tinker with it though, thanks.

-edit-
Nevermind, your code works perfectly, excel just didn't want to work with the colors I used. All is good now.

JimBobtheMonkey on
• Registered User regular
ColorIndex uses a predefined microsoft palette, if you want it to work on any color use Interior.Color to get an RGB value.