Calculating averages if value in another column matches

[strangebiscuit]

I've got worksheets that have information about a different persons utility usage on each row. Column A has the name, Column B has an ID, Column C has the type of utility (GAS or POWER), and D has their usage (just a number).

I need a macro to go through each sheet in the workbook and put separates averages for GAS and POWER usage on each sheet.

I've got something that works by simply looping through the rows and using IF statements to add to counters based on whether Column C lists GAS or POWER.

But I was wondering if there's a better way to do it using formulas or something so that the averages will update if the values are changed in any of the rows. I looked into the AVERAGEIF function but couldn't seem to make anything work. Does anyone have any ideas?

A sample sheet looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]Utility[/TD]
[TD]Usage[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 144"]
<tbody>[TR]
[TD="width: 144"]Jamie Doe[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]43334[/TD]
[TD][TABLE="width: 53"]
<tbody>[TR]
[TD="width: 53"]GAS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]654[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jimmie Doe[/TD]
[TD]22344[/TD]
[TD][TABLE="width: 53"]
<tbody>[TR]
[TD="width: 53"]GAS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]233[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]John Doe[/TD]
[TD]12123[/TD]
[TD][TABLE="width: 53"]
<tbody>[TR]
[TD="width: 53"]POWER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]6553[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Harry Doe[/TD]
[TD]1456444[/TD]
[TD][TABLE="width: 53"]
<tbody>[TR]
[TD="width: 53"]GAS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]156[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Barry Doe[/TD]
[TD]42344[/TD]
[TD][TABLE="width: 53"]
<tbody>[TR]
[TD="width: 53"]POWER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]954[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Willy Doe[/TD]
[TD]212123[/TD]
[TD][TABLE="width: 53"]
<tbody>[TR]
[TD="width: 53"]POWER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]3000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Tabatha Doe[/TD]
[TD]135235[/TD]
[TD][TABLE="width: 53"]
<tbody>[TR]
[TD="width: 53"]GAS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]487[/TD]
[/TR]
</tbody>[/TABLE]


My macro code (not utilizing functions) is the following:

Option Explicit

Sub GetScoreAverage()
 
 Dim Current As Worksheet


 ' Loop through all of the worksheets in the active workbook.
 For Each Current In Worksheets
 
    Dim N As Long, i As Long
    Dim GasCount As Long, GasTotal As Double, PowCount As Long, PowTotal As Double
    N = Current.Cells(Current.Rows.count, "D").End(xlUp).Row
    'Loop through rows on current sheet and count GAS and POWER totals
    For i = 2 To N
        If Current.Cells(i, "C").Value = "GAS" Then
            GasCount = GasCount + 1
            GasTotal = GasTotal + Current.Cells(i, "D").Value
        End If
        If Current.Cells(i, "C").Value = "POWER" Then
            PowCount = PowCount + 1
            PowTotal = PowTotal + Current.Cells(i, "D").Value
        End If
    Next i
    
    'Place averages for both types at the bottom of the sheet
    With Current.Cells(Current.Cells.Find(What:="*", AFter:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2, 1)
      .Resize(2).Value = Application.Transpose(Array("Average Gas", "Average Power"))
      .Resize(2).Font.Bold = True
      ' Average Gas Use Formula
      .Offset(, 1).FormulaR1C1 = GasTotal / GasCount
      ' Average Power Use Formula
      .Offset(1, 1).FormulaR1C1 = PowTotal / PowCount
    End With
    
    'Reset Variables
    N = 0
    i = 0
    GasCount = 0
    GasTotal = 0
    PowCount = 0
    PowTotal = 0
       
 Next


 'Display a message indicating that the macro has finished
 MsgBox "Completed...", vbInformation


End Sub

I feel like there must be a better, simpler and probably obvious solution using functions but I can't seem to puzzle it out. If anyone could advise me or just tell me if I'm being an idiot, I'd be very appreciative.

Thanks very much!

 

[iliace]

The macro looks ok, but if you're not opposed to using a function, it'd be something like this:

=AVERAGEIF(C2:C8, "GAS", D2:D8)

 

[strangebiscuit]

The macro looks ok, but if you're not opposed to using a function, it'd be something like this:

=AVERAGEIF(C2:C8, "GAS", D2:D8)

Ahhh, thank you...don't know why I couldn't figure that out.

So I was able scrap the whole counter section and just change the formulas to:

      ' Average Gas Use Formula      
      .Offset(, 1).FormulaR1C1 = "=AVERAGEIF(R1C3:R[-1]C3, ""GAS"", R1C4:R[-1]C4)"
      ' Average Power Use Formula
      .Offset(1, 1).FormulaR1C1 = "=AVERAGEIF(R1C3:R[-1]C3, ""POWER"", R1C4:R[-1]C4)"

This seems to work much better (my version started throwing an "Overflow" error on big workbooks...not clear why).

Only problem I still seem to have is that on the occasional sheet where there is only one type of utility listed, the formula for the utility that is not present just shows "#DIV/0!" which looks messy. Can you think of any way to avoid that and just make it show "0" or blank or something in this case?

 

[iliace]

Something like this to avoid the error:

.Offset(, 1).FormulaR1C1 = "=IFERROR(AVERAGEIF(R1C3:R[-1]C3, ""GAS"", R1C4:R[-1]C4), 0)"

Couldn't tell you why the Overflow error, you'd have to do some debugging/stepping through the code to figure that out.

Also, I would recommend instead of hard-coding "GAS" and "POWER" instead refer to the cell that contains that text. It's a better approach if the utility type is already in a cell somewhere, and located predictably relative to the formula you are creating.

 

[strangebiscuit]

Something like this to avoid the error:

.Offset(, 1).FormulaR1C1 = "=IFERROR(AVERAGEIF(R1C3:R[-1]C3, ""GAS"", R1C4:R[-1]C4), 0)"

Couldn't tell you why the Overflow error, you'd have to do some debugging/stepping through the code to figure that out.

Also, I would recommend instead of hard-coding "GAS" and "POWER" instead refer to the cell that contains that text. It's a better approach if the utility type is already in a cell somewhere, and located predictably relative to the formula you are creating.

That works beautifully, thanks. I really appreciate your help, you gave me exactly what I needed.

About your last suggestion, I'm not sure I understand. How would I be able to tell what the Utility column for a given row contains unless I specifically check for the value "GAS" or "POWER"?

Pardon if it's painfully obvious or if I'm misunderstanding the suggestion.

 

[iliace]

I'm not sure the suggestion is even applicable to your situation, but this is an example of what I'm talking about:

Sheet1

	A	B	C	D
Name	ID	Utility	Usage
Jamie Doe	GAS
Jimmie Doe	GAS
John Doe	POWER
Harry Doe	GAS
Barry Doe	POWER
Willy Doe	POWER
Tabatha Doe	GAS
GAS
POWER

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:84px;"><col style="width:67px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]43334[/TD]

[TD="align: right"]654[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]22344[/TD]

[TD="align: right"]233[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]12123[/TD]

[TD="align: right"]6553[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]1456444[/TD]

[TD="align: right"]156[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]42344[/TD]

[TD="align: right"]954[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]212123[/TD]

[TD="align: right"]3000[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]135235[/TD]

[TD="align: right"]487[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]383 [/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]3,502 [/TD]

</tbody>

Formeln der Tabelle

Zelle Formel B11 =AVERAGEIF($C$2:$C$8, A11, $D$2:$D$8) <tbody> </tbody>

<tbody>
</tbody>

 

[strangebiscuit]

Ah, I see. We could change the sheet layout a bit to facilitate that if its better...is something likely to go wrong with the hard-coded method?

 

[iliace]

Not any more than variable method, but it's easier to update later and if you have more categories. Just a general programming practice.