VBA: Calculate average based on data from previous column, variable length

**zeno1** · 05-01-2012, 04:17 PM

Hello,

In column C I try to calculate an average from data in column B and the number of cells in column B to be calculated is defined by a variable Time.
To clarify, Time is a variable number that will indicate the first number of rows to be calculated. Column B will not start at row 2 but rather at a unspecified row (approx between say row 20 and 30).
Example:
Time=5
column B: row 21 as first row with a number
in column C: looking in row 25 for average of 5 first values of column B (row 21 to 25)

This value is calculated only once (so no rolling value). I'm looking for a generic formula.
Thanks for your help.

**AlvaroSiza** · 05-01-2012, 06:56 PM

Does the result in C always maintain the current row as the bottom of the range in B to be averaged and extend up by n rows, where n = time.Value (5)?

So if I was in Cell C30 and Time = 10, you want the average of B20:B30?

**zeno1** · 05-01-2012, 07:26 PM

The current row will not be used, because the cursor will be in another location. (Do I understand your question correctly?)
I will execute VBA, that will perform a number of operations on various sheets. One of these operations is this calculation.
This calculation is only done for this particular cell, the first cell of column C in this example. The location of this cell (row #) will be the value 'n' of Time (here 5) lower than where the previous column (B) starts.
All cells above will be empty, except for row 1 that has the titles.
Thanks for your help.