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:
- Current High - Current Low
- |Current High - Previous Close|
- |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 |
Syntax
=AverageTrueRange(Symbol, [Days], [StartDate])Examples
=AverageTrueRange("AAPL")=AverageTrueRange("AAPL", "14")=AverageTrueRange("TSLA", "14")=AverageTrueRange("SPY", "20")=AverageTrueRange("AAPL", "14", DATE(2024,1,15))=AverageTrueRange(A1)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
Why is ATR returning "NA"?
Check that:
- The symbol is valid and actively traded
- There is sufficient historical data for the period
- The stock has OHLC data available
How do I use ATR for stop-losses?
A common approach:
- Trailing stop = Entry Price - (2 x ATR) for longs
- Adjust multiplier based on your risk tolerance
What does a rising/falling ATR mean?
Rising ATR indicates increasing volatility (larger daily ranges), while falling ATR indicates decreasing volatility (smaller daily ranges).
Excel Templates Using Average True Range (ATR)
These ready-made MarketXLS templates call AverageTrueRange() in their worksheet formulas. Open one to see the function working inside a complete model.
Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS
Get started today
▶
How does MarketXLS work?