title: How do I calculate the average of different ranges in the same column in Excel preferably using command button?
body: <p>I'm using Excel 2013. There are around fifty worksheets and I've to calculate hundreds of averages in a worksheet. The data is spread like C3:C10, C12:C16, C18:C35, etc. and I want to calculate the average at the end of each of these ranges like C11, C17, C36, etc. The range always lie within the same column and each range is separated by two blank cells but the range is not fixed. </p>
  
  <p>I tried recording a macro.</p>
  
  <pre><code>Sub Averaging() 
  ActiveCell.FormulaR1C1 = "=AVERAGE(R[-12]C:R[-1]C)"
  End Sub
  </code></pre>
  
  <p>But I don't know how to make R[-12]C part dynamic. I was thinking of creating a command button which after selecting the cell, for example the range is C3:C10 and I select cell C11, and upon pressing the command button it will automatically pick the range and calculate the average. Is it possible?</p>
  
  <p>Any help will be appreciated. VBA, formulas anything.</p>