Conditional weighted average excel vba code required

kiran66

New Member
Joined
Dec 8, 2020
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
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
    Capture.PNG
    30 KB · Views: 10

Some videos you may like

Excel Facts

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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,567
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
  '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

New Member
Joined
Dec 8, 2020
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top