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/

Excel question - resolved

JimBobtheMonkeyJimBobtheMonkey Registered User regular
edited October 2015 in Help / Advice Forum
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

  • DaenrisDaenris 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
    JimBobtheMonkey
  • JimBobtheMonkeyJimBobtheMonkey 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
  • Dis'Dis' 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.

Sign In or Register to comment.