Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Average projection?
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. -- Wild Nerd ------------------------------------------------------------------------ Wild Nerd's Profile: http://www.excelforum.com/member.php...o&userid=32633 View this thread: http://www.excelforum.com/showthread...hreadid=524434 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Average projection?
On Mon, 20 Mar 2006 10:36:43 -0600, Wild Nerd
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 __________________________ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Average projection?
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 -- Wild Nerd ------------------------------------------------------------------------ Wild Nerd's Profile: http://www.excelforum.com/member.php...o&userid=32633 View this thread: http://www.excelforum.com/showthread...hreadid=524434 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Average projection?
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 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 __________________________ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Average projection?
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. -- Wild Nerd ------------------------------------------------------------------------ Wild Nerd's Profile: http://www.excelforum.com/member.php...o&userid=32633 View this thread: http://www.excelforum.com/showthread...hreadid=524434 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Average projection?
On Mon, 20 Mar 2006 13:31:26 -0600, Wild Nerd
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 __________________________ |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Average projection?
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 -- Wild Nerd ------------------------------------------------------------------------ Wild Nerd's Profile: http://www.excelforum.com/member.php...o&userid=32633 View this thread: http://www.excelforum.com/showthread...hreadid=524434 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
30 Day Moving Average Ignoring Blank Cells | Excel Worksheet Functions | |||
how do I extract the moving average trendline point data? | Charts and Charting in Excel | |||
24hr moving average | Excel Discussion (Misc queries) | |||
Moving average | Excel Worksheet Functions | |||
Plotting moving average line on a chart | Charts and Charting in Excel |