How to change average range automatically?
 

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.

How to change average range automatically?
 

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.

How to change average range automatically?
 

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

And if I change D5 from 5 into 6.
B5 should get an average of A2: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.

How to change average range automatically?
 

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.

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.

How to change average range automatically?
 

Thank you. But how about B17 if the data ranging as A1:A20?

"JE McGimpsey" wrote:

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.

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.