Average of multiple ranges based on specific variables

[DavidJRuess]

Hi Everyone,

I'm trying to come up with a simple macro that will scan through a list and calculate the average for a range that has a specific variable.
So it would go through the following table and give me the average for all the red's and then the average for the blue, and then the green, etc... (see table below).

Nothing is fixed. So I might have seventy five different Red numbers today and 45 red numbers tomorrow, sometimes there won't be any red numbers. And the numbers in column B will change each day as well.

I'm hoping to paste these results further down the sheet, or on another sheet (doesn't matter), where it lists the column A variable with the average next to it:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Red
[/TD]
[TD]60
[/TD]
[/TR]
[TR]
[TD]Blue
[/TD]
[TD]77
[/TD]
[/TR]
[TR]
[TD]etc...
[/TD]
[TD]etc..
[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be appreciative. I started trying to use a For, first row to last row, type of formula and using an if, to calculate for when the variable was only Red.

But then I started researching and figured there was a much simpler way to do it using Averageif, or an evaluate, or find method.

Thank you for your help

David




[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[/TR]
[TR]
[TD]Red
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]Red
[/TD]
[TD]37
[/TD]
[/TR]
[TR]
[TD]Red
[/TD]
[TD]132
[/TD]
[/TR]
[TR]
[TD]Red
[/TD]
[TD]56
[/TD]
[/TR]
[TR]
[TD]Blue
[/TD]
[TD]98
[/TD]
[/TR]
[TR]
[TD]Blue
[/TD]
[TD]56
[/TD]
[/TR]
[TR]
[TD]Green
[/TD]
[TD]24
[/TD]
[/TR]
[TR]
[TD]Green
[/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]Green
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]Black
[/TD]
[TD]24
[/TD]
[/TR]
[TR]
[TD]Black
[/TD]
[TD]34
[/TD]
[/TR]
[TR]
[TD]Black
[/TD]
[TD]52
[/TD]
[/TR]
[TR]
[TD]Black
[/TD]
[TD]78
[/TD]
[/TR]
[TR]
[TD]Black
[/TD]
[TD]98
[/TD]
[/TR]
[TR]
[TD]Yellow
[/TD]
[TD]67
[/TD]
[/TR]
</tbody>[/TABLE]

 

[davesexcel]

Are these the color or the word?

 

[DavidJRuess]

They are words.

Thanks very much,
David

 

[davesexcel]

Test this out

Sub GetAverages()
    Dim rd As String, r As Double
    Dim bl As String, b As Double
    Dim gn As String, g As Double
    Dim bk As String, blk As Double
    Dim yw As String, y As Double
    Dim Rws1 As Long, Rng1 As Range
    Dim Rws2 As Long, Rng2 As Range


    Rws1 = Cells(Rows.Count, "A").End(xlUp).Row
    Set Rng1 = Range(Cells(1, 1), Cells(Rws1, 1))

    Rws2 = Cells(Rows.Count, "B").End(xlUp).Row
    Set Rng2 = Range(Cells(1, 2), Cells(Rws2, 2))

    rd = "Red"
    r = WorksheetFunction.AverageIfs(Rng2, Rng1, rd)
    
    bl = "Blue"
    b = WorksheetFunction.AverageIfs(Rng2, Rng1, bl)
    
    gn = "Green"
    g = WorksheetFunction.AverageIfs(Rng2, Rng1, gn)
    
    bk = "Black"
    blk = WorksheetFunction.AverageIfs(Rng2, Rng1, bk)
    
    yw = "Yellow"
    y = WorksheetFunction.AverageIfs(Rng2, Rng1, yw)
    
    Cells(Rows.Count, "A").End(xlUp).Offset(2, 0) = rd
    Cells(Rows.Count, "A").End(xlUp).Offset(0, 1) = r
    Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = bl
    Cells(Rows.Count, "A").End(xlUp).Offset(0, 1) = b
    Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = gn
    Cells(Rows.Count, "A").End(xlUp).Offset(0, 1) = g
    Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = bk
    Cells(Rows.Count, "A").End(xlUp).Offset(0, 1) = blk
    Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = yw
    Cells(Rows.Count, "A").End(xlUp).Offset(0, 1) = y

End Sub

 

[DavidJRuess]

Thanks for the reply.

Will you help me understand what the code is actually doing? I'm a little confused by it, and when I try to modify it to my data it's not working properly.

Thanks

 

[davesexcel]

What part do you not understand?

 

[calvin771]

Setup a summary table outside of your data with each of the colors you want to track and try something like this:

=averageif(A:A,G2,B:B)

Drag the formula down through your table and each cell should find the average of each item you entered in your summary table.

G2 is the cell where your color is in your summary table. Modify the formula accordingly.