CODEWITHSUNDEEP
excelvba
The B
The B

Reputation: 45

VBA excel: Sumproduct to find weighted average

Need help on a simple VBA .

I created one that will look to columns C6 to H6 , if it includes the word "include" then it will average the values in C7 to H7.

The code below works to get the average, how do I upgrade it to do the sumproduct so I get a wt average?

The Excel formula is 

SUMPRODUCT(C7:H7,C8:H8)/SUM(C8:H8)

Now I need to do a wt average. Using sum product I can get the answers, but how will I do it in VBA to make it dynamic and ppl can change the include values

Sub SuperAvgL1()
    Dim columnA As Range
    Dim columnB As Range
    Dim myR
    Set columnA = [C6:H6]
    Set columnB = [C7:H7]
    Set columnC = [C8:H8]

myR = WorksheetFunction.AverageIf(columnA, "Include", columnB)
Range("I7") = myR

End Sub

enter image description here

Upvotes: 0

Views: 483

Answers (1)

Jchang43
Jchang43

Reputation: 891

Just a few things off the bat; ColumnC hasn't been initialized, it's never a bad idea to include Option Explicit because it'll help catch errors later. Also it's a little odd that you're calling your Ranges columns when they're rows. Try this though and see if it works.

Option Explicit

Sub SuperAvgL1()
    Dim rowA() As Variant
    Dim rowB() As Variant
    Dim rowC() As Variant
    Dim myResult As Variant
    Dim i As Long
    Dim Sumprod As Double
    Dim Total As Double

    rowA() = Range("C6:H6").Value
    rowB() = Range("C7:H7").Value
    rowC() = Range("C8:H8").Value

    Sumprod = 0
    Total = 0

    For i = 1 To 6
        If rowA(1, i) = "Include" Then
        Sumprod = Sumprod + rowB(1, i) * rowC(1, i)
        Total = Total + rowC(1, i)
        End if

    Next i
    Range("I7").Value = Sumprod / Total

End Sub

Upvotes: 1

Related Questions