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:

	A	B	C	D
	Name	ID	Utility	Usage
1	Jamie Doe <tbody> </tbody>	43334	GAS <tbody> </tbody>	654
3	Jimmie Doe	22344	GAS <tbody> </tbody>	233
4	John Doe	12123	POWER <tbody> </tbody>	6553
5	Harry Doe	1456444	GAS <tbody> </tbody>	156
6	Barry Doe	42344	POWER <tbody> </tbody>	954
7	Willy Doe	212123	POWER <tbody> </tbody>	3000
8	Tabatha Doe	135235	GAS <tbody> </tbody>	487

<tbody>
</tbody>

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
1	Name	ID	Utility	Usage
2	Jamie Doe	43334	GAS	654
3	Jimmie Doe	22344	GAS	233
4	John Doe	12123	POWER	6553
5	Harry Doe	1456444	GAS	156
6	Barry Doe	42344	POWER	954
7	Willy Doe	212123	POWER	3000
8	Tabatha Doe	135235	GAS	487
9
10
11	GAS	383
12	POWER	3,502

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