How to change average range automatically?
If I understand you correctly,
B5: =AVERAGE(OFFSET(A1,0,0,$D$5,1))
Copy down as necessary
In article ,
Yuanhang wrote:
I made a mistake.
If I change D5 from 5 into 6.
B5 should get an average of A1:A5
B6 = average of A2:A7
B7 = average of A3:A8
B8 = average of A4:A9
"Yuanhang" wrote:
Sorry for the presentation on my question.
Just as what you assume:
Let's assume you have numbers in the range A1:A20
In D5 you have 5.
I want an average in column B like this:
B5 = average of A1:A5
B6 = average of A2:A6
B7 = average of A3:A7
B8 = average of A4:A8
Thank you.
"T. Valko" wrote:
It's not clear what you want.
Let's assume you have numbers in the range A1:A20
In D5 you have 5.
Does that mean you want an average in column B like this:
B5 = average of A1:A5
B10 = average of A6:A10
B15 = average of A11:A15
B20 = average of A16:A20
What if the size of the range in column A is not divisable by the number
in
D5. For example, D5 = 3. The last sequence of 3 would be cell A18.
Biff
"Yuanhang" wrote in message
...
I wanna associate average with a changing number which determines how
many
numbers in group to calculate a average.
For example, in an excel sheet, I have one column full of prices data,
let's
say, Column A. And in the cell D5, there is a initial number: 5 which
means
in column A, we should calculate average mean for every five continuous
prices (like moving average in Finance). If I change Cell D5 into 10, I
want
the data column A was averaged in every 10 prices.
That's is to say, by associated Column A and the cell D5, I may be able
to
use solver to get some valuable information.
I hope I explain the problem clearly. I am very in a hurry to know the
answer. Thank you.
|