• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Calculate ATR in Excel

October 5, 2021 by Andrew Childress
Average true range in Excel

True range is an indicator often used to measure volatility of investments such as stocks. Let’s learn how to calculate ATR in Microsoft Excel.

True range is an indicator often used to measure volatility of investments such as stocks. It’s most often presented in the form of average true range (ATR). Excel makes it easy to calculate the ATR of an investment’s volatility, thanks to the use of formulas. Let’s learn how to calculate ATR in Microsoft Excel.

How to Calculate ATR in Excel

Average true range is calculated as a 14-day moving average in many common scenarios. Let’s imagine fourteen days of true range data for a given investment. It’s first useful to know how true range is calculated. True range takes three measurements of an investment on a daily basis:

  • The price range from highest to lowest within the day
  • The price range from the day’s high to the prior day’s closing price
  • The price range from the day’s low to the prior day’s closing price

The true range is the maximum – largest – of the three range values listed above. As you can see, a true range spreadsheet might look like this.

ATR in Excel

The ATR is a simple moving average consisting of 14 days’ worth of data. In the range here, you’ll simply want to calculate the average of all 14 true range values that you’ve computed.

To do that, click into an empty cell on your worksheet. Type an = to begin a formula. Then, insert the AVERAGE function:

=AVERAGE(

Average true range in Excel

Finally, click and drag to select the cells containing your true range data. Here, this range is B2:B15. Close your parentheses, completing your formula as:

=AVERAGE(B2:B15)

Hit Enter on your keyboard, and Excel returns your true range value:

Calculate ATR in Excel

As you can see, Excel makes it easy to find the average true range (ATR) for a given investment. This is a great tool to measure volatility and illustrate it based on real-world performance data for investments.

Category: Excel FormulasTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Calculate arrears in ExcelHow to Calculate Arrears in Excel
Next Post:RANDARRAY in ExcelRANDARRAY formula in Excel

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top