weighted average VBA Code

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.

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

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)

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
 

Attachments

  • Capture11.JPG
    Capture11.JPG
    58.5 KB · Views: 7

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,551
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
This is images for function.
 

Attachments

  • 11111.jpg
    11111.jpg
    35.7 KB · Views: 2
  • 22222.jpg
    22222.jpg
    76.2 KB · Views: 2

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,551
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Replace this with your code
And add
VBA Code:
Dim x as long
to the first of your code
VBA Code:
 Range("N" & P + 7) = CDate(xCol2.Item(P))
        Range("O" & P + 7) = Round(WorksheetFunction.SumIf(Range("A8:A" & lastRow_R), CDate(xCol2.Item(P)), Range("C8:C" & lastRow_R)), 2)
        x = Application.WorksheetFunction.SumIf(Range("A8:A" & lastRow_R), CDate(xCol2.Item(P)), Range("C8:C" & lastRow_R))
        Range("P" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & CDate(xCol2.Item(P)) & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("D8:D" & lastRow_R).Address & " )") / x, 2)
        Range("Q" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & CDate(xCol2.Item(P)) & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("E8:E" & lastRow_R).Address & " )") / x, 2)
        Range("R" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & CDate(xCol2.Item(P)) & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("F8:F" & lastRow_R).Address & " )") / x, 2)
        Range("S" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & CDate(xCol2.Item(P)) & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("G8:G" & lastRow_R).Address & " )") / x, 2)
        Range("T" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & CDate(xCol2.Item(P)) & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("H8:H" & lastRow_R).Address & " )") / x, 2)
 

kiran66

New Member
Joined
Dec 8, 2020
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
i have tried but i am getting result as "0"
can you try the code and repost the excel here please
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,551
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

you can Test Previous code After This Line
VBA Code:
Range("N" & P + 7) = CDate(xCol2.Item(P))
with
VBA Code:
Debug.print CDate(xCol2.Item(P))
Debug.print CDate(xCol2.Item(P)).value
Debug.print CDate(xCol2.Item(P)).Address

And see what is result at Immediate window
And then Paste result here.

I don't have CDate(xCol2.Item(P))
and Use this code and it working:
VBA Code:
Dim Criteria as Range
        Set Criteria = Range("N" & P + 7)
        Range("O" & P + 7) = Round(WorksheetFunction.SumIf(Range("A8:A" & lastRow_R), Criteria, Range("C8:C" & lastRow_R)), 2)
        x = Application.WorksheetFunction.SumIf(Range("A8:A" & lastRow_R), Criteria, Range("C8:C" & lastRow_R))
        Range("P" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("D8:D" & lastRow_R).Address & " )") / x, 2)
        Range("Q" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("E8:E" & lastRow_R).Address & " )") / x, 2)
        Range("R" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("F8:F" & lastRow_R).Address & " )") / x, 2)
        Range("S" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("G8:G" & lastRow_R).Address & " )") / x, 2)
        Range("T" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("H8:H" & lastRow_R).Address & " )") / x, 2)
 

kiran66

New Member
Joined
Dec 8, 2020
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
i am posting my excel file can you check it once . i don't no why i am getting zeros in the p,q,r,s,t columns

 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,551
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I Check your Macro.
After Line 370
Between 'MsgBox (xCol2.Count) to Before For V = 8 To lastRow_R
Change code to this:
VBA Code:
Dim x As Long
        Dim Criteria As Range
        ActiveWorkbook.Worksheets(r).Activate
        lastRow_R = Worksheets(r).Range("A" & Rows.Count).End(xlUp).Row
     For P = 1 To xCol2.Count
        'MsgBox (xCol2.Item(P))
        Range("N" & P + 7) = CDate(xCol2.Item(P))
        Set Criteria = Range("N" & P + 7)
        Range("O" & P + 7) = Round(WorksheetFunction.SumIf(Range("A8:A" & lastRow_R), CDate(xCol2.Item(P)), Range("C8:C" & lastRow_R)), 2)
        x = Application.WorksheetFunction.SumIf(Range("A8:A" & lastRow_R), CDate(xCol2.Item(P)), Range("C8:C" & lastRow_R))
        Range("P" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.Address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("D8:D" & lastRow_R).Address & " )") / x, 2)
        Range("Q" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.Address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("E8:E" & lastRow_R).Address & " )") / x, 2)
        Range("R" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.Address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("F8:F" & lastRow_R).Address & " )") / x, 2)
        Range("S" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.Address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("G8:G" & lastRow_R).Address & " )") / x, 2)
        Range("T" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.Address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("H8:H" & lastRow_R).Address & " )") / x, 2)
 

kiran66

New Member
Joined
Dec 8, 2020
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
I Check your Macro.
After Line 370
Between 'MsgBox (xCol2.Count) to Before For V = 8 To lastRow_R
Change code to this:
VBA Code:
Dim x As Long
        Dim Criteria As Range
        ActiveWorkbook.Worksheets(r).Activate
        lastRow_R = Worksheets(r).Range("A" & Rows.Count).End(xlUp).Row
     For P = 1 To xCol2.Count
        'MsgBox (xCol2.Item(P))
        Range("N" & P + 7) = CDate(xCol2.Item(P))
        Set Criteria = Range("N" & P + 7)
        Range("O" & P + 7) = Round(WorksheetFunction.SumIf(Range("A8:A" & lastRow_R), CDate(xCol2.Item(P)), Range("C8:C" & lastRow_R)), 2)
        x = Application.WorksheetFunction.SumIf(Range("A8:A" & lastRow_R), CDate(xCol2.Item(P)), Range("C8:C" & lastRow_R))
        Range("P" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.Address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("D8:D" & lastRow_R).Address & " )") / x, 2)
        Range("Q" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.Address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("E8:E" & lastRow_R).Address & " )") / x, 2)
        Range("R" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.Address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("F8:F" & lastRow_R).Address & " )") / x, 2)
        Range("S" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.Address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("G8:G" & lastRow_R).Address & " )") / x, 2)
        Range("T" & P + 7) = Round(Evaluate("=sumProduct(--(" & Range("A8:A" & lastRow_R).Address & " = " & Criteria.Address & "), " _
        & Range("C8:C" & lastRow_R).Address & " , " & Range("H8:H" & lastRow_R).Address & " )") / x, 2)
Thank you very much its working fine
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,551
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You 're Welcome & thanks for feedback.
 

kiran66

New Member
Joined
Dec 8, 2020
Messages
12
Office Version
  1. 2019
Platform
  1. Windows
You 're Welcome & thanks for feedback.
Hii,

can i get the same code in the module 3 line number 53 we are getting average for the total number of quantities here also we need weighted average
 

Watch MrExcel Video

Forum statistics

Threads
1,126,896
Messages
5,621,495
Members
415,844
Latest member
Reda Fouad Ramzy

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