weighted average on particular date VBA code required

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

Hii

thank you for your response, i am working on lot of parameters , weighted averages are one of the part of it. i have completed the macro for remaining part i need to integrate the weighted average macro to remaining macro code. if possible can you send the macro code for 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)

i tried to adapt the sum product formula in my code but I did not get any results from that code. I am unable to find the mistake, if possible can you modify the formula and please post it here.

Hii

i am posting my macro here,

here 2 modules are there in the excel, in sheet1 if we run the macro it will directly split into new sheet based on supervisor name.

in module 1 from line number 376 to 383 is code for the average the values in individual sheet based on their date, here i need to modify to the weighted average

for example:

A.sai kiran sheet name here

"D" column having the some score on individual date, average of "D" column score based on their date is shown in "P"

"E"............................................................................................................."E"................................................................................."Q"

same thing for "F", "G" ..............

hiii

above code is working fine but it shows the weighted average value of 1/12/2020 is showing to the all remaining dates. so code need to modify, results will be shown as per the date.

if possible can you add the code in module 1 from line number 376 to 383 and repost it here