 # Normalized ATR Googles Sheets Formula

###### Why Formulate a Normalized ATR

The Average True Range is a tool for monitoring the volatility of a single stock. However, it cannot be applied when comparing multiple stocks due to the difference in trading prices.

For example, let’s compare two stocks, AMD and INTC.
AMD – Trading at \$98.00 with an ATR(10) of 4.17
INTC – Trading at \$36.00 with an ATR(10) of 1.45
Comparing the volatility using the ATR would be challenging due to the differences in trading prices.

###### Normalized ATR Formula

However, If you calculate the ATR as a percentage of the stock price, you get a volatility measure that is directly comparable across stocks with different prices. We accomplish this by using the formula expressed below.

NATR = (ATR/CLOSE) X 100

NATR Example:
AMD NATR(10) = (4.17/98.00) x 100 = 4.26%
INTC NATR(10) = (1.45/36.00) x 100 = 4.03%

The completed calculation shows that both companies have similar NATRs ( volatility), which is no surprise as both stocks trade in the same industry.

###### Normalized ATR for Analysis

Similar to Standard Deviation, the NATR can be used as a volatility scan/filter for a shortlist, as a market monitor for general volatility, or to measure the risk profile of a potential.

In the scatter chart below, the NATR is used to monitor the of stock volatility of the S&P 100.

###### To calculate the NATR we to need to divide the ATR formula by Yesterday’s Close and then multiple by 100.

B1 = “Stock Code”

``=(AVERAGE(ARRAYFORMULA(QUERY(TRANSPOSE(ABS(QUERY(QUERY({QUERY(GOOGLEFINANCE(B1,"all",TODAY()-30, TODAY()),"SELECT Col3, Col4 ORDER BY Col1 DESC LIMIT 14"),QUERY(GOOGLEFINANCE(B1,"Close",today()-30, 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("A2:A15"))&")"))))/GOOGLEFINANCE(B1,"closeyest"))*100``