Average True Range (ATR)

Returns the Average True Range, a volatility indicator that accounts for gaps by measuring the true range (including gap movements) over a period.

True Range Definition

True Range is the greatest of:

  1. Current High - Current Low
  2. |Current High - Previous Close|
  3. |Current Low - Previous Close|

ATR is the moving average of True Range values.

Common Uses

Use Case Application
Stop-loss placement Set stops at 2-3x ATR from entry
Position sizing Lower position size for high ATR
Volatility comparison Compare volatility across stocks
Breakout confirmation Large moves should exceed ATR

Parameters

Parameter Description Default
Symbol Stock ticker symbol Required
Days Number of periods 14
StartDate Calculate as of this date Today

Examples

=AverageTrueRange("AAPL")
14-day ATR for Apple
=AverageTrueRange("AAPL", "14")
Explicitly 14-day ATR
=AverageTrueRange("TSLA", "14")
ATR for volatile stock
=AverageTrueRange("SPY", "20")
20-day ATR for SPY
=AverageTrueRange("AAPL", "14", DATE(2024,1,15))
Historical ATR
=AverageTrueRange(A1)
Symbol from cell reference

When to Use

  • Set trailing stop-losses based on volatility
  • Position sizing for risk management
  • Identify volatility expansion or contraction
  • Confirm breakout strength
  • Compare relative volatility across stocks

When NOT to Use

Scenario Use Instead
Need close-to-close volatility StandardDeviationOnClosePrice()
Need momentum oscillator RelativeStrengthIndex()
Need trend direction SimpleMovingAverage()
Need directional movement PlusDirectionalIndicator() or MinusDirectionalIndicator()

Common Issues & FAQ

Q: Why is ATR returning "NA"? A: Check that:

  • The symbol is valid and actively traded
  • There is sufficient historical data for the period
  • The stock has OHLC data available

Q: How do I use ATR for stop-losses? A: A common approach:

  • Trailing stop = Entry Price - (2 x ATR) for longs
  • Adjust multiplier based on your risk tolerance

Q: What does a rising/falling ATR mean? A: Rising ATR indicates increasing volatility (larger daily ranges), while falling ATR indicates decreasing volatility (smaller daily ranges).

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today
MarketXLS Excel Add-in Tutorial - How to Use Average True Range (ATR) and Other Financial Formulas
▶
How does MarketXLS work?

Related Functions