title: Auto calculate average over varying number values row by row
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>Here is an example (column H is the average that I am currently calculating manually):</p>
  
  <pre><code>     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
  ...
  </code></pre>
  
  <p>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.</p>
  
  <ul>
  <li>NOTICE how rows are missing between row 5 and 11, and row 12 is missing too? That is because they are filtered out for some other reason. I need to exclude those hidden rows from my calculations and only calculate the average for the rows visible.</li>
  </ul>
  
  <p>Im trying to write a VBA script that will automatically calculate this, then print that average value for each ID in column H.</p>
  
  <p>Here is some code I have considered:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>This seems overly complicated. Its a simple problem in theory, but the missing rows and differing number of ID# repetitions makes it more complex.</p>
  
  <p>If this can be done in an Excel function, that would be even better.</p>
  
  <p>Any thoughts or suggestions would be greatly appreciated. thanks.</p>