creating a simple moving average tool for stock investing?

Try selecting a grid of cells that includes your existing formulas, and extend that grid selection well below the current day. The first row of this selection should be the current day.

Press Ctrl-D to copy those formulas in the top row all the way down the selected grid.

Now, if your formulas are built correctly using absolute positioning where required and relative positioning where required, this should work like a champ.

E.g., starting at row 1, with only one formula to demonstrate:

Date price 5DayAvg

1-Jun $10.00

2-Jun $11.00

3-Jun $12.00 $2.40 formula= =(B2:B6)/5

4-Jun $13.00 $2.60 formula= =(B3:B7)/5

5-Jun $14.00 $2.80 etc.

6-Jun $15.00 $3.00

7-Jun $16.00 $3.20

8-Jun $17.00 $3.40

9-Jun $18.00 $3.60

10-Jun $19.00 $3.80

11-Jun $20.00 $4.00

12-Jun $21.00 $4.20

13-Jun $22.00 $4.40 formula= =(B12:16)/5

OK this is filled out down to row 14.

The last formula is adding in 0's for the non-existent future dates, which isn't strictly correct, but it will do for the example.

I should also point out that for the first column, I used the formula of the row above + 1. That is, the date in cell A14 has the formula =A13+1.

Now, select row 14, A thru C, then drag the selection down some number of rows. However many you think you'll need. Press Ctrl-D.

Now, with the first column set up to be the previous cell + 1, it will actually auto-increment the date. This isn't strictly correct either, since the exchanges aren't open on week-ends. But nevermind that for now. The important thing is that the formula in the third column will automatically calculate relative to the current row, two rows back, and two rows forward, added then divided by 5.

At this point, all you need to do is fill in the prices for each date, and the 5 day average will automatically update as you enter them. As soon as you get to the end of your pre-entered dates and formulas, just repeat the process and extend it down further.

Hope that proves helpful.

1

Values in col C, formula in D5:-

=IF(ISNUMBER(C6),"", IF(ISNUMBER(C5),AVERAGE(C1:C5),""))

Now drag this formula down column D as far as you like. It will only show the average of the latest five values. Do the same with your other moving averages.

you received't flow incorrect with Scottrade. they are decrease priced, grant countless sturdy study approaches that others charge for, and they paintings properly. i have been a shopper of theirs for a year and am extremely satisfied, even thinking shifting different money owed to them.