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:

=AVERAGE(B2:B31)

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”:

=IF(B2>C2,”Over”,”Under”)

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