| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Average
Hi Expert,
I think you have heard this term before in Finanical market- technical analysis. I have a series of stock price data. Is it possible that say I put a number 6 in a cell and then it can sum 6 rows data and then divide by 6 too. Another example .... I put 9 in cell A1. Stock price series data in B1 to B20 ... it will sum B1 to B9 and then divide by 9 too. Look like it is conditional sum depending on the number in a cell. Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Moving Average
Try the below to sum B1 to the number of rows specified in A1
=SUM(OFFSET(B1,0,0,A1,1)) If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Expert, I think you have heard this term before in Finanical market- technical analysis. I have a series of stock price data. Is it possible that say I put a number 6 in a cell and then it can sum 6 rows data and then divide by 6 too. Another example .... I put 9 in cell A1. Stock price series data in B1 to B20 ... it will sum B1 to B9 and then divide by 9 too. Look like it is conditional sum depending on the number in a cell. Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Moving Average
Hi,
=SUM(B1:INDEX(B1:B20,A1))/A1 Mike "Elton Law" wrote: Hi Expert, I think you have heard this term before in Finanical market- technical analysis. I have a series of stock price data. Is it possible that say I put a number 6 in a cell and then it can sum 6 rows data and then divide by 6 too. Another example .... I put 9 in cell A1. Stock price series data in B1 to B20 ... it will sum B1 to B9 and then divide by 9 too. Look like it is conditional sum depending on the number in a cell. Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Moving Average
'to average
=AVERAGE(OFFSET(B1,0,0,A1,1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below to sum B1 to the number of rows specified in A1 =SUM(OFFSET(B1,0,0,A1,1)) If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Expert, I think you have heard this term before in Finanical market- technical analysis. I have a series of stock price data. Is it possible that say I put a number 6 in a cell and then it can sum 6 rows data and then divide by 6 too. Another example .... I put 9 in cell A1. Stock price series data in B1 to B20 ... it will sum B1 to B9 and then divide by 9 too. Look like it is conditional sum depending on the number in a cell. Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Moving Average
Hi Jacob,
It works .. Thanks so much. Regards, Elton "Jacob Skaria" wrote: 'to average =AVERAGE(OFFSET(B1,0,0,A1,1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below to sum B1 to the number of rows specified in A1 =SUM(OFFSET(B1,0,0,A1,1)) If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Expert, I think you have heard this term before in Finanical market- technical analysis. I have a series of stock price data. Is it possible that say I put a number 6 in a cell and then it can sum 6 rows data and then divide by 6 too. Another example .... I put 9 in cell A1. Stock price series data in B1 to B20 ... it will sum B1 to B9 and then divide by 9 too. Look like it is conditional sum depending on the number in a cell. Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Moving Average
Hi Jacob,
I have column problem. Say the column format is like this. C1 is 6 Then I can work out the MA depending on the number in C1. Your formula looks like it is meant for forward calculation. Mine is backward from the latest date (current price) to the rows depending on the number in cell C1. Can you make it please? Thanks indeed. Column A Column B Column C Date Price 6 13/10/2009 0.455 14/10/2009 0.46 15/10/2009 0.455 16/10/2009 0.54 19/10/2009 0.51 20/10/2009 0.55 21/10/2009 0.56 22/10/2009 0.54 Moving average (15-22 Oct if it's 6-day MA) 23/10/2009 0.55 Moving average (16-23 Oct if it's 6-day MA) 27/10/2009 0.53 Moving average (19-27 Oct if it's 6-day MA) "Jacob Skaria" wrote: 'to average =AVERAGE(OFFSET(B1,0,0,A1,1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below to sum B1 to the number of rows specified in A1 =SUM(OFFSET(B1,0,0,A1,1)) If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Expert, I think you have heard this term before in Finanical market- technical analysis. I have a series of stock price data. Is it possible that say I put a number 6 in a cell and then it can sum 6 rows data and then divide by 6 too. Another example .... I put 9 in cell A1. Stock price series data in B1 to B20 ... it will sum B1 to B9 and then divide by 9 too. Look like it is conditional sum depending on the number in a cell. Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Moving Average
Elton, thanks for your feedback. With your data try the below
=AVERAGE(OFFSET(B1,COUNTA(B:B),0,-C1-1,1)) If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Jacob, I have column problem. Say the column format is like this. C1 is 6 Then I can work out the MA depending on the number in C1. Your formula looks like it is meant for forward calculation. Mine is backward from the latest date (current price) to the rows depending on the number in cell C1. Can you make it please? Thanks indeed. Column A Column B Column C Date Price 6 13/10/2009 0.455 14/10/2009 0.46 15/10/2009 0.455 16/10/2009 0.54 19/10/2009 0.51 20/10/2009 0.55 21/10/2009 0.56 22/10/2009 0.54 Moving average (15-22 Oct if it's 6-day MA) 23/10/2009 0.55 Moving average (16-23 Oct if it's 6-day MA) 27/10/2009 0.53 Moving average (19-27 Oct if it's 6-day MA) "Jacob Skaria" wrote: 'to average =AVERAGE(OFFSET(B1,0,0,A1,1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below to sum B1 to the number of rows specified in A1 =SUM(OFFSET(B1,0,0,A1,1)) If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Expert, I think you have heard this term before in Finanical market- technical analysis. I have a series of stock price data. Is it possible that say I put a number 6 in a cell and then it can sum 6 rows data and then divide by 6 too. Another example .... I put 9 in cell A1. Stock price series data in B1 to B20 ... it will sum B1 to B9 and then divide by 9 too. Look like it is conditional sum depending on the number in a cell. Thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Moving Average
Hi Jacob,
It only works for the last row of data. The second last and the third last did not work. What should I do please? I have tried Mike one ... does not work either .... Thanks Elton "Jacob Skaria" wrote: Elton, thanks for your feedback. With your data try the below =AVERAGE(OFFSET(B1,COUNTA(B:B),0,-C1-1,1)) If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Jacob, I have column problem. Say the column format is like this. C1 is 6 Then I can work out the MA depending on the number in C1. Your formula looks like it is meant for forward calculation. Mine is backward from the latest date (current price) to the rows depending on the number in cell C1. Can you make it please? Thanks indeed. Column A Column B Column C Date Price 6 13/10/2009 0.455 14/10/2009 0.46 15/10/2009 0.455 16/10/2009 0.54 19/10/2009 0.51 20/10/2009 0.55 21/10/2009 0.56 22/10/2009 0.54 Moving average (15-22 Oct if it's 6-day MA) 23/10/2009 0.55 Moving average (16-23 Oct if it's 6-day MA) 27/10/2009 0.53 Moving average (19-27 Oct if it's 6-day MA) "Jacob Skaria" wrote: 'to average =AVERAGE(OFFSET(B1,0,0,A1,1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below to sum B1 to the number of rows specified in A1 =SUM(OFFSET(B1,0,0,A1,1)) If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Expert, I think you have heard this term before in Finanical market- technical analysis. I have a series of stock price data. Is it possible that say I put a number 6 in a cell and then it can sum 6 rows data and then divide by 6 too. Another example .... I put 9 in cell A1. Stock price series data in B1 to B20 ... it will sum B1 to B9 and then divide by 9 too. Look like it is conditional sum depending on the number in a cell. Thanks |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Moving Average
Hi all,
I tried to amend and use =Sum(Offet(B20,0,0,-C1,1))/C1 Work now ... Thanks for all. B20 means start from the bottom cell .... Anyway, thanks "Jacob Skaria" wrote: Elton, thanks for your feedback. With your data try the below =AVERAGE(OFFSET(B1,COUNTA(B:B),0,-C1-1,1)) If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Jacob, I have column problem. Say the column format is like this. C1 is 6 Then I can work out the MA depending on the number in C1. Your formula looks like it is meant for forward calculation. Mine is backward from the latest date (current price) to the rows depending on the number in cell C1. Can you make it please? Thanks indeed. Column A Column B Column C Date Price 6 13/10/2009 0.455 14/10/2009 0.46 15/10/2009 0.455 16/10/2009 0.54 19/10/2009 0.51 20/10/2009 0.55 21/10/2009 0.56 22/10/2009 0.54 Moving average (15-22 Oct if it's 6-day MA) 23/10/2009 0.55 Moving average (16-23 Oct if it's 6-day MA) 27/10/2009 0.53 Moving average (19-27 Oct if it's 6-day MA) "Jacob Skaria" wrote: 'to average =AVERAGE(OFFSET(B1,0,0,A1,1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below to sum B1 to the number of rows specified in A1 =SUM(OFFSET(B1,0,0,A1,1)) If this post helps click Yes --------------- Jacob Skaria "Elton Law" wrote: Hi Expert, I think you have heard this term before in Finanical market- technical analysis. I have a series of stock price data. Is it possible that say I put a number 6 in a cell and then it can sum 6 rows data and then divide by 6 too. Another example .... I put 9 in cell A1. Stock price series data in B1 to B20 ... it will sum B1 to B9 and then divide by 9 too. Look like it is conditional sum depending on the number in a cell. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Moving average | Excel Worksheet Functions | |||
| moving average | Excel Discussion (Misc queries) | |||
| moving average | Excel Worksheet Functions | |||
| Moving average | Charts and Charting in Excel | |||
| Moving average | Excel Worksheet Functions | |||
![Elton Law[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
