How to Calculate the Average True Range in Excel

Generic Formula to Calculate the Average True Range

The stock’s daily high, daily low, and daily close price are needed to calculate the average true range.

Daily Range = High Price – Low Price

To consider the closing price of the previous day:

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

It chooses the maximum among the three differences:

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

The calculation of the average true range for the first period is:

ATR_t = (1/n) ∑_iTR_i

Welles proposed using a smoothed average of 14 days. So, in the above formula, n equals 14. The average of the true range is calculated for the first 14 days, and from that result, the first average true range is returned. The next ATRs will be calculated using an exponential moving average with the following formula:

ATR_t = ( ATR_t-1* (n-1) + TR_t ) / n

ATR_t is the present average true range.
ATR_t-1 is the previous average true range.
n represents 14 (for a 14-day moving average).
TR_t is the present true range.

This is the final formula:

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 shown by ATR.

This dataset showcases a report on the Stock Price of the “ABC” Company. It includes the High, Low, and Close prices of stock from 24 March 2021 to 26 April 2021. Freeze Panes was used.

how to calculate average true range in excel

Step1 – Determine the Present High Minus the Present Low

Steps:

Select F5 and enter the following formula.

=C5-D5

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

Press ENTER.

Determine Present High Minus Present Low

At the bottom-right corner of F5, the cursor will display a plus (+) sign. It’s the Fill Handle tool. Double-click it.

Using Fill Handle Tool

This is the output.

Determine Present High Minus Present Low to Calculate Average True Range in Excel

Read More: How to Get Average Time in Excel

Step 2 – Calculate the Present High Minus the Previous Close

Steps:

Select G6 and enter the following formula.

=ABS(C6-E5)

C6 and E5 represent the present high price and the previous close price. The ABS function returns the absolute value of their difference. It always returns a positive value.

Press ENTER.

Calculate Present High Minus Previous Close

Step 3 – Compute the Present Low Minus the Previous Close

Steps:

Select H6 and enter the following formula.

=ABS(D6-E5)

Press ENTER.

Compute Present Low Minus Previous Close

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

Step 4 – Calculate the True Range

Steps:

Select I5 and enter the following formula.

=MAX(F5:H5)

F5:H5 represents values of high-low, high-previous close, and low-previous close.
The MAX function returns the maximum value among data in F5:H5.

Press ENTER.

Enumerate True Range

Check if the formula returns the correct answer in I6 . 5.37, 1.48, and 3.89 are displayed in F6, G6, and H6.

5.37 is the greatest value. The function also returns this value.

Enumerate True Range to Calculate Average True Range in Excel

Step 5 – Calculate the Average True Range

Steps:

Select J18 and enter the following formula.

=AVERAGE(I5:I18)

The AVERAGE function returns the arithmetic mean of the arguments.

Press ENTER.

Calculate Average True Range

J5:J17 was kept blank.

Go back to the generic formula: the ATR for the first period is calculated using the arithmetic mean of consecutive 14 days. 14 days of data were stored in cells I5:I18.

To determine the ATR for the following days:

Select J19 and enter the following formula.

=(J18*13+I19)/14

The formula is explained in the previous section.

Press ENTER.

How to Calculate ATR Stop Loss

ATR can assist you in determining where to set your stop loss.
Below is a part of the previous dataset with calculated ATR values.

How to Calculate ATR Stop Loss

The second multiple of the ATR value was used here. Calculate the stop loss for each date:

Steps:

Create a new column: Stop Loss.
Select G5 and enter the following formula.

=E5-2*F5

Press ENTER.

You purchased stock on 13 April 2021 at the close price: $258.49.

You set a stop loss at $249.26: if the price of this stock falls on this, you won’t take any further risk and sell it.
If you buy it on 21 April at a price of $260.58, you will set the stop loss at $252.07.

Calculating Stop Loss from Average True Range in Excel