 # 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

## Files

• Hi,

There is no need for a macro to work out Averages ...

Hope this will help ## Files

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner • 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)

• You are welcome ...

Your test file does not contain any macro ... so it was difficult to guess you needed a calculation to be corrected ... Can you adapt the Sumproduct formula which produces the weighted Averages ...into your existing code ...?

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner • 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.

• 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.

Hello,

Would you mind posting your macro ... and pointing out what needs to be revised ... If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner • 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" ..............

## Files

• Hello,

To give you an example of how to work out the weighted average in cell P8

Code
``````Sub TestWeightedAverage()
Range("P8").Value = Evaluate( _
"=SUMPRODUCT((\$C\$8:\$C\$12)*(D\$8:D\$12)*(\$A\$8:\$A\$12=\$N8))/SUMPRODUCT((\$C\$8:\$C\$12)*(\$A\$8:\$A\$12=\$N8))")
End Sub``````

Hope this will help If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner • 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

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner 