If you are a stock market investor or keep up to date about the commodities market, then you’ve obviously heard the word ATR which is the average true range. It’s one kind of market indicator which points out the price volatility of a product. In this article, we’ll talk about this and show you how to calculate the average true range in Excel. So, stay with us throughout.

**Table of Contents**hide

## Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

## What Is Average True Range (ATR)?

In 1978, J. Welles Wilder introduced this technique as an indicator of volatility. The disparity between a stock’s highest and lowest price on any given day is known as its range, and it is frequently employed as a volatility indicator. However, everybody suspends trading frequently if prices change significantly on any given day. We occasionally observe it in the trade of commodities, and it may result in a difference between the opening and closing prices of two successive days. This information wouldn’t always be included in the daily range. Here comes the necessity of ATR. It displays the typical price fluctuation of an asset over a specified time. The indicator can be used by investors to choose the ideal trading period. Even though we use it to forecast trends, it cannot tell which way the price will move. However, the ATR is currently utilized for all future contracts.

## Generic Formula to Calculate Average True Range

We require a stock’s daily high, daily low, and daily close price in order to calculate the average true range. At first, we have to know the daily range of stock. It’s nothing difficult at all. Simply, it’s the difference between the high and low prices of a single day.

**Daily Range = High Price – Low Price**

But we’ve to consider another thing in our mind if yesterday’s closing price doesn’t fit in the daily range. Then, the formula of true range converts into the following.

**True Range = MAX[(High-Low),ABS(High-Prev. Close),ABS(Low-Prev. Close)]**

That means it chooses the maximum among these three differences. To put it simply in words, the true average is the maximum of the following three.

- The present high minus the present low
- The absolute value of present high minus previous close
- The absolute value of present low minus previous close

The calculation of the average true range for the first period is like the following.

**ATR**

_{t}**= (1/n) ∑**

_{i}TR

_{i}Welles proposed using a smoothed average of 14 days. So, in the above formula, **n** equals **14**. Firstly, we’ve to average the true range for the first 14 days, and from that, we’ll get our first average true range. After that, the next ATRs will be calculated using an exponential **moving average**. In this case, we’ll utilize the following formula.

**ATR**

_{t}**= ( ATR**

_{t-1 }*** (n-1) + TR**

_{t}**) / n**

Here, **ATR**** _{t}** means the present average true range.

**ATR**

**means the previous average true range.**

_{t-1}**n**represents

**14**because we’re using a 14-day moving average.

**TR**

**serves as the present true range.**

_{t}So, finally, the formula looks like the one below.

**ATR**

_{t}**= ( ATR**

_{t-1 }*** 13 + TR**

_{t}**) / 14**

The standard value of **n** is **14**, as the average volatility over the previous 14 days is exposed by ATR, and it is thought that a 14-day period provides the most accurate outcomes.

## 5 Steps to Calculate Average True Range in Excel

Let’s assume we have a report on the **Stock Price of “ABC” Company** in our hand. This dataset includes the **High**, **Low**, and **Close** prices of this certain stock from **24 March 2021** to **26 April 2021**. We’ve used **Freeze Panes** to accommodate the full picture of the dataset.

Now, we’ll show how to calculate the average true range from this data in Excel.

Here, we have used *Microsoft Excel 365* version, you may use any other version according to your convenience. Let us know if you face any problems while doing the same work in any other version of Excel.

### Step 01: Determine Present High Minus Present Low

At the very beginning, we’ll calculate the daily ranges. To do this, follow the steps carefully.

**📌**** Steps:**

- Firstly, select cell
**F5**and enter the following formula into that cell.

`=C5-D5`

Here, **C5** and **D5** represent the High and Low prices of the corresponding day.

- Secondly, press the
**ENTER**key.

- Thirdly, bring the cursor to the bottom-right corner of cell
**F5**and it will look like a plus**(+)**sign. It’s the**Fill Handle**tool. Then, double-click on it to use the tool.

Wondrously, it fills the following cells till cell **F27** with correct results. Using this tool saves us a lot of time.

**Read More:** **How to Calculate Time Range in Excel (4 Handy Methods)**

### Step 02: Calculate Present High Minus Previous Close

Then, we’ll calculate the second part of the **formula for the true range** stated above. Let’s see it in action.

**📌**** Steps:**

- Initially, select cell
**G6**and write down the following formula.

`=ABS(C6-E5)`

Here, **C6** and **E5** represent the present high price and the previous close price respectively. The **ABS function** returns the absolute value of their difference. That means it always gives a positive value.

- After that, press
**ENTER**.

**Read More: How to Calculate Range in Excel (5 Handy Methods)**

### Step 03: Compute Present Low Minus Previous Close

In this section, we’ll find out the third part of the **formula for the true range** stated above. Let’s see the process in detail.

**📌**** Steps:**

- Primarily, go to cell
**H6**and paste the formula below.

`=ABS(D6-E5)`

- Secondarily, hit
**ENTER**.

Here, cells **G5** and **H5** are blank because there is no previous day before these days.

**Read More:** **How to Average Negative and Positive Numbers in Excel**

### Step 04: Enumerate True Range

Now, from the results we get in the previous three steps, we’ll calculate the true range. It’s simple & easy, just follow along.

**📌**** Steps:**

- At first, select cell
**I5**and put the following formula into the cell.

`=MAX(F5:H5)`

In this formula, **F5:H5** represents the cells with values of high-low, high-previous close, and low-previous close.

Here, the **MAX function** returns the maximum value among data in cells **F5**, **G5**, and **H5**.

- As usual, tap the
**ENTER**key.

Similarly, we can check for cell **I6** if the formula returns the correct answer or not. There are **5.37**, **1.48**, and **3.89** in cells **F6**, **G6**, and **H6** correspondingly. Easily, we can see that **5.37** is the greatest among them. And the function also returns the same value in cell **I6**.

**Read More: How to Calculate Range for Grouped Data in Excel (3 Effective Methods)**

### Step 05: Calculate Average True Range

Finally, the phase has come. We’ll get the desired average true range after completing this section.

**📌**** Steps:**

- First of all, select cell
**J18**and paste the formula below.

`=AVERAGE(I5:I18)`

In this case, the **AVERAGE function** returns the arithmetic mean of the arguments.

- As always, hit
**ENTER**.

Why have we kept the cells in the **J5:J17** blank? Any idea? Just get back to the **generic formula** we discussed before. There we’ve said that we’ll calculate the ATR for the first period using the arithmetic mean of consecutive **14** days. So, here, we’ve used 14 days of data in cells in the **I5:I18** range.

- Then, we’ve to determine ATR for the following days. To do this, go to cell
**J19**and enter the following formula.

`=(J18*13+I19)/14`

We also explained this formula in the **previous section** of this article.

- Accordingly, hit the
**ENTER**key.

**Read More: How to Calculate Average of Top 5 Values in Excel (5 Methods)**

## How to Calculate ATR Stop Loss

We just learned to figure out how to calculate the average true range in Excel. ATR can assist you in determining where to set your stop loss. A correctly positioned stop loss takes care of losing too much by acting as insurance. Your stop loss will likely be broader if you’re dealing with a stock with a high ATR because the stock will likely experience larger daily price swings.

We can utilize multiples of the ATR reading (2x/3x), and at this point, you can choose the market you’re trading in accordance with your risk appetite. There isn’t a fixed ideal number. You must choose it based on the state of the market and your prior knowledge.

Now, we have taken a portion of our previous dataset with the corresponding calculated ATR values.

As a thumb rule, we took the second multiple of the ATR value. Then, we’ll calculate the stop loss for each date. So, without further delay, let’s dive in!

**📌**** Steps:**

- Firstly, create a new column
**Stop Loss**. - Here, go to cell
**G5**and enter the formula below.

`=E5-2*F5`

- Then, press
**ENTER**.

For clarification, we’re explaining it now. Let’s say you purchased stock on **13 April 2021** at the close price of this stock which is **$258.49**. Then, you will set a stop loss at **$249.26** which means if the price of this stock falls on this value of stop loss, you won’t take any further risk and sell this stock.

Similarly, if someone buys it on **21 April** at a price of **$260.58**, then he will set his stop loss at **$252.07** if he takes the second multiple of the ATR reading.

**Read More: How to Calculate Date Range in Excel**

## Practice Section

For doing practice by yourself we have provided a **Practice** section on the last sheet. Please do it by yourself.

## Conclusion

This article provides easy and brief solutions on how to calculate the average true range in Excel. Don’t forget to download the **Practice** file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website **Exceldemy** to explore more.

## Related Articles

**How to Calculate Interquartile Range in Excel (2 Suitable Ways)****Calculate Percentage Range in Excel (Step-by-Step Guide)****How to Average Filtered Data in Excel (2 Easy Methods)****Calculate Weekly Average in Excel (4 Easy Ways)****How to Do Subtotal Average in Excel (5 Suitable Ways)****Calculate Average Numbers in Excel (9 Handy Methods)****How to Calculate Average Share Price in Excel (2 Easy Ways)**