# Conditional weighted average excel vba code required

#### kiran66

##### New Member
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)

#### Attachments

• Capture.PNG
30 KB · Views: 10

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

##### Well-known Member
Welcome to MrExcel Message Board
Test this:

VBA Code:
``````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

For i = 2 To l
Set Criteria = Cells(i, 10)
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

##### New Member

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

Replies
21
Views
460
Replies
1
Views
29
Replies
14
Views
199
Replies
2
Views
35
Replies
3
Views
95

1,126,981
Messages
5,621,952
Members
415,869
Latest member
LWSkinner

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back