Average True Range


The average true range (ATR) is a technical analysis indicator used to measure market volatility. The ATR was developed by J. Welles Wilder and is based on the true range of security, which is defined as the greatest of the following:

  • The difference between the current high and the current low.
  • The difference between the previous close and the current high.
  • The difference between the previous close and the current low.

The ATR is calculated by taking the average of the true ranges over a specified period of time, typically 14 days. The resulting value is used to gauge the level of price volatility, with higher values indicating greater volatility and lower values indicating lower volatility. Traders may use the ATR to help set stop loss levels and to identify potential price breakouts.


Cell B1 = Stock Code

=AVERAGE(ARRAYFORMULA(QUERY(TRANSPOSE(ABS(QUERY(QUERY({QUERY(GOOGLEFINANCE(B1,"all",TODAY()-25, TODAY()),"SELECT Col3, Col4 ORDER BY Col1 DESC LIMIT 14"),QUERY(GOOGLEFINANCE(B1,"Close",today()-25, today()),"SELECT Col2 ORDER BY Col1 DESC LIMIT 14 OFFSET 1 LABEL Col2 'Closeyest'")}),"SELECT Col1-Col2, Col1-Col3, Col3-Col2 "))),"SELECT max(Col"&JOIN(",max(Col",row(indirect("A1:A15"))&")"))))

Google Sheets, Stock Price ATR calculator
Google Sheets, ATR Calculator


ATR Formula Tutorial

Back To Top