Conditional Sum - Moving Average

E

Elton Law

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
 
O

OssieMac

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?
 
J

Joe User

Elton Law said:
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.
Click to expand...

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 -----
 
E

Elton Law

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 said:
Elton Law said:
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.
Click to expand...

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 said:
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
Click to expand...

.
Click to expand...
 
J

Joe User

Elton Law said:
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) .
Click to expand...

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 said:
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 said:
Elton Law said:
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.
Click to expand...

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 said:
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
Click to expand...

.
Click to expand...
Click to expand...
 
E

Elton Law

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(OFFSET(B3,0,0,1,$B$1)),"")
Thanks so much for help ....



Joe User said:
Elton Law said:
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) .
Click to expand...

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 said:
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 said:
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 -----

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


.
Click to expand...
Click to expand...
Click to expand...
 
J

Joe User

Elton Law said:
I slightly change to this [....]
=IF(COUNT(OFFSET(B3,0,0,1,$B$1))=$B$1,
AVERAGE(OFFSET(B3,0,0,1,$B$1)),"")
Click to expand...

Good pick up! The constant 3 was definitely a mistake, not what I intended.


----- original message -----

Elton Law said:
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(OFFSET(B3,0,0,1,$B$1)),"")
Thanks so much for help ....



Joe User said:
Elton Law said:
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) .
Click to expand...

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 said:
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 ....



:

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 -----

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


.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using a reference to calculate moving average 3
Conditional/variable sum?? 7
Average/Sum formula with offset 9
calculating moving average 1
Moving Average 8
function question (sumif countif conditional) 0
Help on randomly selecting cells to SUM 2
Possible Conditional Formatting 1

Top