Izzy Schneider Izzy Schneider - 6 days ago 6
MySQL Question

Calculate average value in VBA

I need to calculate the average value of different numbers in VBA, but it has become complicated and I'm not sure what to do next.

I turned to Google to find potential solutions to my problem, however I didn't find anything relevant.

Here is my table:

Table example

Each ID:


  • ...is a non-unique number.

  • ...has an associated Value.



The average values for each ID need to be calculated in VBA.

Example


  • The values with an Id of 'ID1' are 2,3 and 4, and so have an average of 3

  • The values with an Id of 'ID2' are both 4, and so have an average of 4

  • ...



What I have now either calculates average value for a whole column or simply repeats the value.

I would be very grateful for help and advice.

Answer

Fair enough. If you have your heart set on a VBA solution this one should do the trick (for your example)...

Sub Av()

Dim LastRow As Long

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow

    Cells(i, 3).Value = Application.WorksheetFunction.SumIf(Range("A2:A" & LastRow), Range("A" & i), Range("B2:B" & LastRow)) / _
Application.WorksheetFunction.CountIf(Range("A2:A" & LastRow), Range("A" & i))

Next

End Sub

This solution will preserve your table rows in the example you posted (as non unique by ID)