How to change average range automatically?
Ok.....
Assume your numbers are in the range A5:A20
D5 = some number
Enter this formula in B5 and copy down to B20:
=IF(OR(D$5="",ROWS(A$5:A$20)+1-D$5<ROWS($1:1)),"",AVERAGE(OFFSET(A$5,ROWS($1:1)-1,,D$5)))
Biff
"Yuanhang" wrote in message
...
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.
|