As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

JimBobtheMonkey
Registered User regular

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

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

0

## Posts

My VBA is rusty, but based on the code you have, something like:

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).

Daenrison-edit-

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

JimBobtheMonkeyon