CODEWITHSUNDEEP
excelvba
brno792
brno792

Reputation: 6799

Auto calculate average over varying number values row by row

I have an Excel file with several columns in it and many rows. One column, say A has ID numbers. Another column, say G has prices. Column A has repeating ID numbers, however not all numbers repeat the same amount of times. Sometimes just once, other times 2, 3 or several times. Each column G for that row has a unique price.

Basically, I need to average those prices for a given ID in column A. If each ID was repeated the same number of times, this would be quite simple, but because they are not I have to manually do my average calculation for each grouping. Since my spreadsheet has many many rows, this is taking forever.

Here is an example (column H is the average that I am currently calculating manually):

     A     ...   G      H
1    1234        3.00   3.50
2    1234        4.00
3    3456        2.25   3.98
4    3456        4.54
5    3456        5.15
11   8890        0.70   0.95
13   8890        1.20
...

So in the above example, the average price for ID# 1234 would be 3.50. Likewise, the average price for ID# 3456 would be 3.98 and for #8890 would be 0.95.

Im trying to write a VBA script that will automatically calculate this, then print that average value for each ID in column H.

Here is some code I have considered:

Sub calcAvg()
Dim rng As Range
Set rng = Range("sheet1!A1:A200003")
    For Each Val In rng
        Count = 0
        V = Val.Value  '''V is set equal to the value within the range
        If Val.Value = V Then
            Sum = Sum + G.Value
            V = rng.Offset(1, 0)  '''go to next row
            Count = Count + 1
        Else
            '''V = Val.Value  '''set value in this cell equal to the value in the next cell down.
            avg = Sum / Count
            H = avg  '''Column G gets the avg value.
        End If
    Next Val

End Sub

I know there are some problems with the above code. Im not too familiar with VBA. Also this would print the avg on the same line everytime. Im not sure how to iterate the entire row.

This seems overly complicated. Its a simple problem in theory, but the missing rows and differing number of ID# repetitions makes it more complex.

If this can be done in an Excel function, that would be even better.

Any thoughts or suggestions would be greatly appreciated. thanks.

Upvotes: 2

Views: 8847

Answers (2)

matzone
matzone

Reputation: 5719

In my way ..

Sub calcAvg()
Dim x, y, i, y2, t, Count, Mount As Integer
Dim Seek0 As String

x = 1 '--> means Col A
y = 1 '--> means start - Row 1
y2 = 7 '--> means end - Row 19

    For i = y To y2
        If i = y Then
            Seek0 = Cells(i, x)
            t = i
            Count = Cells(i, x + 6)
            Mount = 1            
        Else
            If Cells(i, x) <> Seek0 Then
                Cells(t, x + 7) = Count / Mount
                Count = Cells(i, x + 6)
                Mount = 1
                t = i
                Seek0 = Cells(i, x)
            Else
                Count = Count + Cells(i, x + 6)
                Mount = Mount + 1
            End If
        End If
    Next    
End Sub

Hope this helps ..

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53166

If you can add another row to the top of your data (put column Headers in it) its quite simple with a formula.

Formula for C2 is

=IF(A2<>A1,AVERAGEIFS(B:B,A:A,A2),"")

copy this down for all data rows.

This applies for Excel 2007 or later. If using Excel 2003 or earlier, use AVERAGEIF instead, adjusting ranges accordingly

If you can't add a header row, change the first formula (cell C1) to

=AVERAGEIFS(B:B,A:A,A1)

Upvotes: 4

Related Questions