Rolling Average In Excel

Excel spreadsheets (.xls)

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to for professional help and Excel Tutorials

One of the most researched numbers for stock investors and traders is a moving average. What is it? The average end of day price for a stock in the past 30 days for example. Why does it matter? Because many investors consider that breaking through that average is significant. Let’s take a look at how I’d calculate that price. First, I downloaded the stock price closes from Google Finance for Apple (AAPL) as you can see here:

Then, I added a column for the 30 day moving average and simply used the “average” function:


Then, I dragged the formula down as you can see here:

Then, I thought it would be interesting to determine days when the stock moves through the rolling average. How? First, I will determine if the stock is above or below the average by using an “if function”:


Then, I can simply make it a “nested” function, by checking if the previous day had the same result. If it didn’t, that means the stock broke through its rolling average. I will simply change it as follows:

=IF(B2>C2,IF(B3>C3,””,”Stock Breaks To The Upside”),IF(B3

You can also download the spreadsheet here


Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss



Please Leave a Comment!

Please note: Comments may be moderated. It may take a while for them to show on the page.