Moving Average projection?

[Wild Nerd]

I have a column of numbers in which I created 2 different Moving Average time frames.

Now I want to know:

How to write the formula:

What number is needed today for the moving averages to cross each other? For instance, if a shorter moving average is 108 and a longer one is 123, What number is needed today for the shorter average to be larger than the longer average? Keep in mind, I have to drop the oldest time frame price.

[Richard Buttrey]

On Mon, 20 Mar 2006 10:36:43 -0600, Wild Nerd
<Wild.Nerd.24z8on_1142872805.3574@excelforum-nospam.com> wrote:

>
>I have a column of numbers in which I created 2 different Moving Average
>time frames.
>
>Now I want to know:
>
>How to write the formula:
>
>What number is needed today for the moving averages to cross each
>other? For instance, if a shorter moving average is 108 and a longer
>one is 123, What number is needed today for the shorter average to be
>larger than the longer average? Keep in mind, I have to drop the
>oldest time frame price.

Could you post some example data to help better understand your
requirement?

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Wild Nerd]

What price needs to trade today for the 2-Day Moving Average to cross lets say the 18-Day?

576.3750 2-Day M Avg.
579.0000 3-Day M Avg.
581.8500 5-Day M Avg.
583.7500 7-Day M Avg.
589.2292 12-Day M Avg.
589.4583 18-Day M Avg.
593.4667 30-Day M Avg.
592.3313 40-Day M Avg.


I plug these numbers in manually every day
Most recent Day 1 576.50
2 576.25
3 584.25
4 587.75
5 584.50
6 589.25
7 587.75
8 584.00
9 590.00
10 600.50
11 606.00
12 604.00
13 591.00
14 594.00
15 593.00
16 590.25
17 584.75
18 586.50
19 601.00
20 614.50
21 604.25
22 599.00
23 596.00
24 597.75
25 596.00
26 601.75
27 591.25
28 588.00
29 607.50
30 596.75
31 592.25
32 606.75
33 609.50
34 599.75
35 584.50
36 578.00
37 581.00
38 585.00
39 577.75
40 574.75

[Richard Buttrey]

I assume A1:A40 is your data. How do the numbers at the top relate to
the data? i.e. what are the 8 rows and how do they relate to the data
rows?

What for instance do the two summary columns of numbers mean and what
are you trying to achieve in columns C & D

Rgds


On Mon, 20 Mar 2006 12:23:28 -0600, Wild Nerd
<Wild.Nerd.24zdjn_1142879104.9228@excelforum-nospam.com> wrote:

>
>576.50 576.3750 2-Day M Avg. Less Than
>576.38 579.0000 3-Day M Avg. Less Than
>581.19 581.8500 5-Day M Avg. Less Than
>583.08 583.7500 7-Day M Avg. Less Than
>587.89 589.2292 12-Day M Avg. Greater Than
>589.63 589.4583 18-Day M Avg. Greater Than
>593.35 593.4667 30-Day M Avg. Greater Than
>592.78 592.3313 40-Day M Avg. Greater Than
>
>
>1 576.50
>2 576.25
>3 584.25
>4 587.75
>5 584.50
>6 589.25
>7 587.75
>8 584.00
>9 590.00
>10 600.50
>11 606.00
>12 604.00
>13 591.00
>14 594.00
>15 593.00
>16 590.25
>17 584.75
>18 586.50
>19 601.00
>20 614.50
>21 604.25
>22 599.00
>23 596.00
>24 597.75
>25 596.00
>26 601.75
>27 591.25
>28 588.00
>29 607.50
>30 596.75
>31 592.25
>32 606.75
>33 609.50
>34 599.75
>35 584.50
>36 578.00
>37 581.00
>38 585.00
>39 577.75
>40 574.75

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Wild Nerd]

Thanks Richard for taking the time to address my problem.

If A1:A40 are my dates. A1 is yesterday and A40 is 40 business days ago.
and B1:B40 are the closing prices for those dates.

A 2-day Average is simply =average(B1:B2), a 5-day is =Average(B1:B5)

I am a trader who wants to be long if the shorter average is above the longer average and short if the shorter average is below the longer average. Let's say I currently own a stock. I want my Excel worksheet to tell me during the current day, what price needs to trade today to put the short avg below the long avg? Eventually, that day's data will be placed into cell B1 after B1-B40 is Ctrl C, arrow down, CTRL V'd.

[Richard Buttrey]

On Mon, 20 Mar 2006 13:31:26 -0600, Wild Nerd
<Wild.Nerd.24zgsb_1142883304.5185@excelforum-nospam.com> wrote:

>
>Thanks Richard for taking the time to address my problem.
>
>If A1:A40 are my dates. A1 is yesterday and A40 is 40 business days
>ago.
>and B1:B40 are the closing prices for those dates.
>
>A 2-day Average is simply =average(B1:B2), a 5-day is =Average(B1:B5)
>
>I am a trader who wants to be long if the shorter average is above the
>longer average and short if the shorter average is below the longer
>average. Let's say I currently own a stock. I want my Excel worksheet
>to tell me during the current day, what price needs to trade today to
>put the short avg below the long avg? Eventually, that day's data
>will be placed into cell B1 after B1-B40 is Ctrl C, arrow down, CTRL
>V'd.

OK, Thanks,

I just need to understand what you mean by shorter average and longer
average - in terms of the data in your original post. I'm also not
quite clear how your data results in the Less Than and Greater Than
results your OP suggests.

e.g. in the first column for the 2 day moving average (row 1 576.5 -
what are these values incidentally, how are they calculated?), the
moving avg of 576.375 is less than the number in the first column,
however on the second row, the 579 3 day moving avg is more than the
576.38 in the first column. Yet your OP marks these both as 'Less
Than'.

Your OP talked about the moving averages 'crossing each other. Could
you just expand on that please, again with ref to your original data.

I think the Solver Add-in is probably needed for a solution here, but
I just need to fully understand your data first.

Sorry for being a bit wooden about this.

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Wild Nerd]

Richard,
In the OP, the price 576.5 is the closing price for Soybeans at the Chicago Board of Trade. I currently plug in these prices(column AE) daily.

These are the actual formulas in the spreadsheet. Does this make better sense?

=AVERAGE(AE41:AE42)-- (Shorter Avg(2-Day))
=AVERAGE(AE41:AE43)-- (Shorter Avg(3-Day))
=AVERAGE(AE41:AE45)-- (Shorter Avg(5-Day))
=AVERAGE(AE41:AE47)-- (Shorter Avg(7-Day))
=AVERAGE(AE41:AE52)-- (Longer Avg(12-Day))
=AVERAGE(AE41:AE58)-- (Longer Avg(18-Day))
=AVERAGE(AE41:AE70)-- (Longer Avg(30-Day))
=AVERAGE(AE41:AE80)-- (Longer Avg(40-Day))

AD AE
1 576.5
=+AD41+1 576.25
=+AD42+1 584.25
=+AD43+1 587.75
=+AD44+1 584.5
=+AD45+1 589.25
=+AD46+1 587.75
=+AD47+1 584
=+AD48+1 590
=+AD49+1 600.5
=+AD50+1 606
=+AD51+1 604
=+AD52+1 591
=+AD53+1 594
=+AD54+1 593
=+AD55+1 590.25
=+AD56+1 584.75
=+AD57+1 586.5
=+AD58+1 601
=+AD59+1 614.5
=+AD60+1 604.25
=+AD61+1 599
=+AD62+1 596
=+AD63+1 597.75
=+AD64+1 596
=+AD65+1 601.75
=+AD66+1 591.25
=+AD67+1 588
=+AD68+1 607.5
=+AD69+1 596.75
=+AD70+1 592.25
=+AD71+1 606.75
=+AD72+1 609.5
=+AD73+1 599.75
=+AD74+1 584.5
=+AD75+1 578
=+AD76+1 581
=+AD77+1 585
=+AD78+1 577.75
=+AD79+1 574.75
=+AD80+1 574.75
=+AD81+1 580.75