"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


.