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)





  • 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 "Thumbs Up" 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 "Thumbs Up" 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" ..............

  • 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 "Thumbs Up" 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!