|
Conditional Sum - Moving Average
Dear Expert,
Want to calculate Moving Average in this format. But Sum is conditional Say B2 to G2 are dates like this. Say B3 to G3 are the prices. A3 is Stock name A2 is empty Can you advise how to make it ...? If I put a 3 in B1 ... Then it can calculate the 3-day moving average in C1. If I put a 7 in D1 ... then it can calculate the 7-day moving average in E1 Moving average is dependent on the cell address to work out. I have asked similar question before ... but the date is in vertical format. This time .... If I present in horizontal format ... hope expert can still make it .... Thanks 21-Mar 22-Mar 23-Mar 24-Mar 25-Mar 26-Mar Stock Name 1.7 1.6 1.75 1.63 1.77 1.42 |
Conditional Sum - Moving Average
Hi Elton,
I cannot understand what you are trying to achieve. "But Sum is conditional." What do you mean by this? "Moving average is dependent on the cell address to work out" What do you mean by this? What cells will be included in the 3 day moving average? What cell does it start on? What cells will be included in the 7 day moving average? What cell does it start on? -- Regards, OssieMac "Elton Law" wrote: Dear Expert, Want to calculate Moving Average in this format. But Sum is conditional Say B2 to G2 are dates like this. Say B3 to G3 are the prices. A3 is Stock name A2 is empty Can you advise how to make it ...? If I put a 3 in B1 ... Then it can calculate the 3-day moving average in C1. If I put a 7 in D1 ... then it can calculate the 7-day moving average in E1 Moving average is dependent on the cell address to work out. I have asked similar question before ... but the date is in vertical format. This time .... If I present in horizontal format ... hope expert can still make it .... Thanks 21-Mar 22-Mar 23-Mar 24-Mar 25-Mar 26-Mar Stock Name 1.7 1.6 1.75 1.63 1.77 1.42 |
Conditional Sum - Moving Average
"Elton Law" wrote:
Say B3 to G3 are the prices. [....] Can you advise how to make it ...? If I put a 3 in B1 ... Then it can calculate the 3-day moving average in C1. I presume you mean a trailing simple moving average. If the prices are in row 3 starting with column B, put the following formula into C1 and copy across: =IF(COLUMN(D3)-COLUMN($B$3)=$B$1,AVERAGE(OFFSET(D3,0,-$B$1,1,$B$1)),"") If you want a leading SMA, then put the following into C1 and copy across: =IF(COUNT(OFFSET(C3,0,0,1,$B$1))=3,AVERAGE(OFFSET( C3,0,0,1,$B$1)),"") ----- original message ----- "Elton Law" wrote in message ... Dear Expert, Want to calculate Moving Average in this format. But Sum is conditional Say B2 to G2 are dates like this. Say B3 to G3 are the prices. A3 is Stock name A2 is empty Can you advise how to make it ...? If I put a 3 in B1 ... Then it can calculate the 3-day moving average in C1. If I put a 7 in D1 ... then it can calculate the 7-day moving average in E1 Moving average is dependent on the cell address to work out. I have asked similar question before ... but the date is in vertical format. This time .... If I present in horizontal format ... hope expert can still make it .... Thanks 21-Mar 22-Mar 23-Mar 24-Mar 25-Mar 26-Mar Stock Name 1.7 1.6 1.75 1.63 1.77 1.42 |
Conditional Sum - Moving Average
Hi Expert,
I am so sorry. I did not make the question clearly. Should be this. A B C D E F G 3 1.6833 26-Mar 24-Mar 23-Mar 22-Mar 21-Mar 20-Mar Stock 1.7 1.6 1.75 1.63 1.77 1.42 B2 to G2 is date 26 Mar to 20 Mar B3 to G3 is Price. If I type 3 in B1, then it can calculate the 3-day simple moving average. In this case it is 1.6833 If I type 5 in B1, then it can work out 5-day simple moving average. In this case, it is 1.690 If I type 6 in B1, then it can work out 6-day simple moving average. In this case, it is 1.6450 Starting point is the latest day which is 26 Mar (in this case) .... Hope you can help ... Thanks .... "Joe User" wrote: "Elton Law" wrote: Say B3 to G3 are the prices. [....] Can you advise how to make it ...? If I put a 3 in B1 ... Then it can calculate the 3-day moving average in C1. I presume you mean a trailing simple moving average. If the prices are in row 3 starting with column B, put the following formula into C1 and copy across: =IF(COLUMN(D3)-COLUMN($B$3)=$B$1,AVERAGE(OFFSET(D3,0,-$B$1,1,$B$1)),"") If you want a leading SMA, then put the following into C1 and copy across: =IF(COUNT(OFFSET(C3,0,0,1,$B$1))=3,AVERAGE(OFFSET( C3,0,0,1,$B$1)),"") ----- original message ----- "Elton Law" wrote in message ... Dear Expert, Want to calculate Moving Average in this format. But Sum is conditional Say B2 to G2 are dates like this. Say B3 to G3 are the prices. A3 is Stock name A2 is empty Can you advise how to make it ...? If I put a 3 in B1 ... Then it can calculate the 3-day moving average in C1. If I put a 7 in D1 ... then it can calculate the 7-day moving average in E1 Moving average is dependent on the cell address to work out. I have asked similar question before ... but the date is in vertical format. This time .... If I present in horizontal format ... hope expert can still make it .... Thanks 21-Mar 22-Mar 23-Mar 24-Mar 25-Mar 26-Mar Stock Name 1.7 1.6 1.75 1.63 1.77 1.42 . |
Conditional Sum - Moving Average
"Elton Law" wrote:
If I type 3 in B1, then it can calculate the 3-day simple moving average. In this case it is 1.6833 If I type 5 in B1, then it can work out 5-day simple moving average. In this case, it is 1.690 If I type 6 in B1, then it can work out 6-day simple moving average. In this case, it is 1.6450 Starting point is the latest day which is 26 Mar (in this case) . To do just what you ask for: =AVERAGE(OFFSET(B3,0,0,1, B1)) To make that a leading simple moving average starting in C1 using data starting in B3, my first formula should work with the following change: =IF(COUNT(OFFSET(B3,0,0,1,$B$1))=3,AVERAGE(OFFSET( B3,0,0,1,$B$1)),"") Copy across for a moving average. I started the moving average with C3 because I usually put the moving average above the beginning (for leading) or ending (for trailing) data point for the period. Since you put the period length in B1, I thought the leading moving average would start with the data in C3. My previous formula for the trailing SMA does use data starting in B3. The reference to D3 (for the formula in C1) is a trick to avoid having to write $B$1-1 and -$B$1+1. ----- original message ----- "Elton Law" wrote: Hi Expert, I am so sorry. I did not make the question clearly. Should be this. A B C D E F G 3 1.6833 26-Mar 24-Mar 23-Mar 22-Mar 21-Mar 20-Mar Stock 1.7 1.6 1.75 1.63 1.77 1.42 B2 to G2 is date 26 Mar to 20 Mar B3 to G3 is Price. If I type 3 in B1, then it can calculate the 3-day simple moving average. In this case it is 1.6833 If I type 5 in B1, then it can work out 5-day simple moving average. In this case, it is 1.690 If I type 6 in B1, then it can work out 6-day simple moving average. In this case, it is 1.6450 Starting point is the latest day which is 26 Mar (in this case) .... Hope you can help ... Thanks .... "Joe User" wrote: "Elton Law" wrote: Say B3 to G3 are the prices. [....] Can you advise how to make it ...? If I put a 3 in B1 ... Then it can calculate the 3-day moving average in C1. I presume you mean a trailing simple moving average. If the prices are in row 3 starting with column B, put the following formula into C1 and copy across: =IF(COLUMN(D3)-COLUMN($B$3)=$B$1,AVERAGE(OFFSET(D3,0,-$B$1,1,$B$1)),"") If you want a leading SMA, then put the following into C1 and copy across: =IF(COUNT(OFFSET(C3,0,0,1,$B$1))=3,AVERAGE(OFFSET( C3,0,0,1,$B$1)),"") ----- original message ----- "Elton Law" wrote in message ... Dear Expert, Want to calculate Moving Average in this format. But Sum is conditional Say B2 to G2 are dates like this. Say B3 to G3 are the prices. A3 is Stock name A2 is empty Can you advise how to make it ...? If I put a 3 in B1 ... Then it can calculate the 3-day moving average in C1. If I put a 7 in D1 ... then it can calculate the 7-day moving average in E1 Moving average is dependent on the cell address to work out. I have asked similar question before ... but the date is in vertical format. This time .... If I present in horizontal format ... hope expert can still make it .... Thanks 21-Mar 22-Mar 23-Mar 24-Mar 25-Mar 26-Mar Stock Name 1.7 1.6 1.75 1.63 1.77 1.42 . |
Conditional Sum - Moving Average
Hi Joe,
Thanks for help. Get exactly what I want ... I slightly change to this .... and I can work out the SMA more easily. =IF(COUNT(OFFSET(B3,0,0,1,$B$1))=$B$1,AVERAGE(OFFS ET(B3,0,0,1,$B$1)),"") Thanks so much for help .... "Joe User" wrote: "Elton Law" wrote: If I type 3 in B1, then it can calculate the 3-day simple moving average. In this case it is 1.6833 If I type 5 in B1, then it can work out 5-day simple moving average. In this case, it is 1.690 If I type 6 in B1, then it can work out 6-day simple moving average. In this case, it is 1.6450 Starting point is the latest day which is 26 Mar (in this case) . To do just what you ask for: =AVERAGE(OFFSET(B3,0,0,1, B1)) To make that a leading simple moving average starting in C1 using data starting in B3, my first formula should work with the following change: =IF(COUNT(OFFSET(B3,0,0,1,$B$1))=3,AVERAGE(OFFSET( B3,0,0,1,$B$1)),"") Copy across for a moving average. I started the moving average with C3 because I usually put the moving average above the beginning (for leading) or ending (for trailing) data point for the period. Since you put the period length in B1, I thought the leading moving average would start with the data in C3. My previous formula for the trailing SMA does use data starting in B3. The reference to D3 (for the formula in C1) is a trick to avoid having to write $B$1-1 and -$B$1+1. ----- original message ----- "Elton Law" wrote: Hi Expert, I am so sorry. I did not make the question clearly. Should be this. A B C D E F G 3 1.6833 26-Mar 24-Mar 23-Mar 22-Mar 21-Mar 20-Mar Stock 1.7 1.6 1.75 1.63 1.77 1.42 B2 to G2 is date 26 Mar to 20 Mar B3 to G3 is Price. If I type 3 in B1, then it can calculate the 3-day simple moving average. In this case it is 1.6833 If I type 5 in B1, then it can work out 5-day simple moving average. In this case, it is 1.690 If I type 6 in B1, then it can work out 6-day simple moving average. In this case, it is 1.6450 Starting point is the latest day which is 26 Mar (in this case) .... Hope you can help ... Thanks .... "Joe User" wrote: "Elton Law" wrote: Say B3 to G3 are the prices. [....] Can you advise how to make it ...? If I put a 3 in B1 ... Then it can calculate the 3-day moving average in C1. I presume you mean a trailing simple moving average. If the prices are in row 3 starting with column B, put the following formula into C1 and copy across: =IF(COLUMN(D3)-COLUMN($B$3)=$B$1,AVERAGE(OFFSET(D3,0,-$B$1,1,$B$1)),"") If you want a leading SMA, then put the following into C1 and copy across: =IF(COUNT(OFFSET(C3,0,0,1,$B$1))=3,AVERAGE(OFFSET( C3,0,0,1,$B$1)),"") ----- original message ----- "Elton Law" wrote in message ... Dear Expert, Want to calculate Moving Average in this format. But Sum is conditional Say B2 to G2 are dates like this. Say B3 to G3 are the prices. A3 is Stock name A2 is empty Can you advise how to make it ...? If I put a 3 in B1 ... Then it can calculate the 3-day moving average in C1. If I put a 7 in D1 ... then it can calculate the 7-day moving average in E1 Moving average is dependent on the cell address to work out. I have asked similar question before ... but the date is in vertical format. This time .... If I present in horizontal format ... hope expert can still make it .... Thanks 21-Mar 22-Mar 23-Mar 24-Mar 25-Mar 26-Mar Stock Name 1.7 1.6 1.75 1.63 1.77 1.42 . |
Conditional Sum - Moving Average
"Elton Law" wrote:
I slightly change to this [....] =IF(COUNT(OFFSET(B3,0,0,1,$B$1))=$B$1, AVERAGE(OFFSET(B3,0,0,1,$B$1)),"") Good pick up! The constant 3 was definitely a mistake, not what I intended. ----- original message ----- "Elton Law" wrote in message ... Hi Joe, Thanks for help. Get exactly what I want ... I slightly change to this .... and I can work out the SMA more easily. =IF(COUNT(OFFSET(B3,0,0,1,$B$1))=$B$1,AVERAGE(OFFS ET(B3,0,0,1,$B$1)),"") Thanks so much for help .... "Joe User" wrote: "Elton Law" wrote: If I type 3 in B1, then it can calculate the 3-day simple moving average. In this case it is 1.6833 If I type 5 in B1, then it can work out 5-day simple moving average. In this case, it is 1.690 If I type 6 in B1, then it can work out 6-day simple moving average. In this case, it is 1.6450 Starting point is the latest day which is 26 Mar (in this case) . To do just what you ask for: =AVERAGE(OFFSET(B3,0,0,1, B1)) To make that a leading simple moving average starting in C1 using data starting in B3, my first formula should work with the following change: =IF(COUNT(OFFSET(B3,0,0,1,$B$1))=3,AVERAGE(OFFSET( B3,0,0,1,$B$1)),"") Copy across for a moving average. I started the moving average with C3 because I usually put the moving average above the beginning (for leading) or ending (for trailing) data point for the period. Since you put the period length in B1, I thought the leading moving average would start with the data in C3. My previous formula for the trailing SMA does use data starting in B3. The reference to D3 (for the formula in C1) is a trick to avoid having to write $B$1-1 and -$B$1+1. ----- original message ----- "Elton Law" wrote: Hi Expert, I am so sorry. I did not make the question clearly. Should be this. A B C D E F G 3 1.6833 26-Mar 24-Mar 23-Mar 22-Mar 21-Mar 20-Mar Stock 1.7 1.6 1.75 1.63 1.77 1.42 B2 to G2 is date 26 Mar to 20 Mar B3 to G3 is Price. If I type 3 in B1, then it can calculate the 3-day simple moving average. In this case it is 1.6833 If I type 5 in B1, then it can work out 5-day simple moving average. In this case, it is 1.690 If I type 6 in B1, then it can work out 6-day simple moving average. In this case, it is 1.6450 Starting point is the latest day which is 26 Mar (in this case) .... Hope you can help ... Thanks .... "Joe User" wrote: "Elton Law" wrote: Say B3 to G3 are the prices. [....] Can you advise how to make it ...? If I put a 3 in B1 ... Then it can calculate the 3-day moving average in C1. I presume you mean a trailing simple moving average. If the prices are in row 3 starting with column B, put the following formula into C1 and copy across: =IF(COLUMN(D3)-COLUMN($B$3)=$B$1,AVERAGE(OFFSET(D3,0,-$B$1,1,$B$1)),"") If you want a leading SMA, then put the following into C1 and copy across: =IF(COUNT(OFFSET(C3,0,0,1,$B$1))=3,AVERAGE(OFFSET( C3,0,0,1,$B$1)),"") ----- original message ----- "Elton Law" wrote in message ... Dear Expert, Want to calculate Moving Average in this format. But Sum is conditional Say B2 to G2 are dates like this. Say B3 to G3 are the prices. A3 is Stock name A2 is empty Can you advise how to make it ...? If I put a 3 in B1 ... Then it can calculate the 3-day moving average in C1. If I put a 7 in D1 ... then it can calculate the 7-day moving average in E1 Moving average is dependent on the cell address to work out. I have asked similar question before ... but the date is in vertical format. This time .... If I present in horizontal format ... hope expert can still make it .... Thanks 21-Mar 22-Mar 23-Mar 24-Mar 25-Mar 26-Mar Stock Name 1.7 1.6 1.75 1.63 1.77 1.42 . |
| All times are GMT +1. The time now is 04:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com