Izzy Schneider - 6 months ago 47

MySQL Question

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:

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)

Source (Stackoverflow)