Moving Average projection?

[Wild Nerd]

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

Now I want to know:

How to write the formula:

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

 

[Richard Buttrey]

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]

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

 

[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

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 day
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 th
longer average and short if the shorter average is below the longe
average. Let's say I currently own a stock. I want my Excel workshee
to tell me during the current day, what price needs to trade today t
put the short avg below the long avg? Eventually, that day's dat
will be placed into cell B1 after B1-B40 is Ctrl C, arrow down, CTR
V'd

 

[Richard Buttrey]

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 th
Chicago Board of Trade. I currently plug in these prices(column AE
daily.

These are the actual formulas in the spreadsheet. Does this mak
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.7