Conditional weighted average excel vba code required

[kiran66]

Hii

i have an excel sheet and iam trying to get the weighted averages for the 4 parameters based on their date, if any one can solve my question it would help to my work. i need macros code for calculating weighted average

i have attached example here (I have the date in A,B,C,D,E,F.....H & I required the weighted average in the P,Q,R,S,T Based on the date)

 

[maabadi]

Welcome to MrExcel Message Board
Test this:

Sub Sumweighted()
Dim i As Variant
Dim j As Variant
Dim l As Long
Dim d As Long
Dim a As Range
Dim b As Range
Dim c As Range
Dim y As Range
Dim Criteria As Range
Dim LasTRow As Long

j = Application.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp)))

With CreateObject("Scripting.Dictionary")
For Each i In j
.Item(i) = i
Next
Cells(1, 10).Resize(.Count) = Application.Transpose(.Keys)
Columns(10).NumberFormat = "MM/dd/yyyy"
End With


  LasTRow = Range("A" & Rows.Count).End(xlUp).Row
  Set a = Range("A2:A" & LasTRow)
  Set b = Range("C2:C" & LasTRow)
  l = Range("J" & Rows.Count).End(xlUp).Row
  'data1 = a.Address
  'data2 = b.Address
  
  
For i = 2 To l
Set Criteria = Cells(i, 10)
'crite = Criteria.Address
Range("K" & i).Value = Application.WorksheetFunction.SumIf(a, Criteria, b)
 Set y = Range("K" & i)
 For d = 12 To 16
  Set c = Range(Cells(2, d - 8), Cells(Range("C" & Rows.Count).End(xlUp).Row, d - 8))
  
  Cells(i, d).Formula = "=sumProduct(--(" & a.Address & " = " & Criteria.Address & "), " & b.Address & " , " & c.Address & " ) / " & y.Address & ""
  Cells(i, d).NumberFormat = "#,#0.0#"
  Next d
Next i
End Sub

 

[kiran66]

Hii Maabadi

Thank you for your response


Range("Q" & P + 7) = Round(WorksheetFunction.AverageIf(Range("A8:A" & lastRow_R), CDate(xCol2.Item(P)), Range("E8:E" & lastRow_R)), 2)
Range("R" & P + 7) = Round(WorksheetFunction.AverageIf(Range("A8:A" & lastRow_R), CDate(xCol2.Item(P)), Range("F8:F" & lastRow_R)), 2)
Range("S" & P + 7) = Round(WorksheetFunction.AverageIf(Range("A8:A" & lastRow_R), CDate(xCol2.Item(P)), Range("G8:G" & lastRow_R)), 2)


upto now i did the simple average by using this code. can you modify this simple average code to weighted average code

Here
A Column indicates-Date
E,F&G Column Indicates- Parameters
C Column indicates -Weight for respected item
Q,R,S Column indicates - average results to be shown here