How to Calculate the Simple Moving Average in Google Sheets

How to Calculate the Simple Moving Average in Google Sheets

Calculating the Simple Moving Average in your Google Sheets document is useful as it makes your spreadsheet dynamic and flexible over time. It becomes increasingly easy to get lost in the rows and columns of data if you’re not careful with hard coding formulas and ranges. That’s why in the case of the Simple Moving Average, it’s best to use a changing formula while you use your spreadsheet. In this article, we will show you how to calculate the simple moving average in Google Sheets.

What is the Simple Moving Average? It is one of the indicators used in technical analysis of trading and investing. It is called a “moving” average because you track the change of the value over time, usually a given number of days in finance. People often turn to it as it is simple to understand and even easier to construct.

For example, you are handling a chart focused on a certain stock and are keeping track of the changes every week, or every 7 days. After a while, the data list gets so long and the numbers can be so similar – it’s easy to lose track!

So, how should we proceed?

To accomplish our goal, we will use a combination of the GOOGLEFINANCE and the AVERAGE function of Google Sheets.

 

The Anatomy of the GOOGLEFINANCE and AVERAGE Function in Google Sheets

To get data on stock prices, we can use the GOOGLEFINANCE function. The syntax (the way we write) the GOOGLEFINANCE function is as follows:

=GOOGLEFINANCE(ticker,[attribute],[start_date],[end_date|num_days],[interval]))

Let’s break the function down to understand each term:

  • = the equal sign is how we begin any function in Google Sheets.
  • GOOGLEFINANCEis our function. This is what we will use to fetch current or historical data from Google Finance.
  • ticker is the ticker symbol for the company you want to analyze. Note that you need to specify which exchange symbol if you want to avoid some changes. In this example, “NASDAQ:GOOG” and “GOOG” both work.
  • attribute is the attribute you want to retrieve from the Google Finance information. If left blank, it automatically retrieves price.
  • start_date is the first date when you want to get the historical data.
  • end_date|num_days is the end date when you want to get the historical data. Alternatively, you can add the number of days after the start_date.
  • interval is the frequency of the data. This is either “DAILY” or “WEEKLY.”

The syntax of the AVERAGE function is simple:

=AVERAGE(value1,[value2,...])
  • = the equal sign is how we begin any function in Google Sheets.
  • AVERAGE is our function.
  • value1is the first value or range you want the function to consider.

We will combine these functions together to calculate the Simple Moving Average in Google Sheets.

 

A Real Example of Calculating the Moving Average

Let’s look at this example below to see how to use calculate moving averages in Google Sheets.

In this simple example, you want to find the 4-month moving average of the orders your business has received. You want to see the trend since you started in January 2019.

How to Calculate the Simple Moving Average in Google Sheets

The column with the cell reference is:

=AVERAGE(C3:C6)

This formula has been copy-pasted down the column in order to calculate the average of the past 4-month’s performance.

This simple problem can be practiced. Use the link below to use our spreadsheet sample:

 

How to Calculate the Simple Moving Average in Google Sheets

Since Simple Moving Average is a finance tool, our example will feature the combination of the GOOGLEFINANCE and the AVERAGE functions.

  1. Choose where you want your Google Finance data to populate. In this example, we picked cell B4. Type in the equal sign and our function, GOOGLEFINANCE.How to Calculate the Simple Moving Average in Google Sheets
  2. Read through the required inputs of the function.How to Calculate the Simple Moving Average in Google Sheets
  3. We want Alphabet Inc’s ticker symbol, “GOOG”. We want the information for the daily price for the past 28 days.How to Calculate the Simple Moving Average in Google Sheets
  4. Hit enter and the data should populate your sheets, with the proper headers. Note that there are only 19 entries. Google Finance omits any non-trading days from the set.How to Calculate the Simple Moving Average in Google Sheets
  5. Next, create another column for your Simple Moving Average. Input the AVERAGE function.How to Calculate the Simple Moving Average in Google Sheets
  6. Input the range, adding the previous 7 daily entries.How to Calculate the Simple Moving Average in Google Sheets
  7. Hit enter.How to Calculate the Simple Moving Average in Google Sheets
  8. Use the lower right handle to fill the rest of the column with the same formula. Note that the formula preserves the 7-day previous range.How to Calculate the Simple Moving Average in Google Sheets
  9. Ta-da, you’re done!How to Calculate the Simple Moving Average in Google Sheets

There you have it! You are now able to highlight a set of alternate rows in Google Sheets as you wish, without disturbing the integrity of your data. Now that you have a grasp on how to combine data visualization styles in your spreadsheets, you can combine this with other Google Sheets formulas to make really powerful data documents!

 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content. There will be no spam and you can unsubscribe at any time.

0 Shares:
Leave a Reply

Your email address will not be published.

You May Also Like