Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding an Average line (from data sheet) to a chart automatically. | Charts and Charting in Excel | |||
Average-Range Change Each Month | Excel Worksheet Functions | |||
Average of a , < range | Excel Worksheet Functions | |||
how do i make a date change automatically if i change one before . | Excel Discussion (Misc queries) | |||
Average Range | Excel Worksheet Functions |