Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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.




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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.




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,624
Default 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.





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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.




  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding an Average line (from data sheet) to a chart automatically. Judi Charts and Charting in Excel 5 January 4th 07 02:31 PM
Average-Range Change Each Month Jani Excel Worksheet Functions 6 August 10th 06 02:16 PM
Average of a , < range Deb Pingel Excel Worksheet Functions 4 December 22nd 05 01:37 AM
how do i make a date change automatically if i change one before . dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 10:21 PM
Average Range Stndt Excel Worksheet Functions 0 October 27th 04 06:00 PM


All times are GMT +1. The time now is 12:50 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"