# Normalized ATR Googles Sheets Formula

Last Updated on 27/05/2024 by Ndanileka

## Why Formulate a Normilzed ATR

The Average True Range (ATR) is a valuable tool for gauging a single stock’s volatility. However, it becomes less helpful when comparing multiple stocks because the ATR value is influenced by the trading price.

For instance, comparing the volatility of chipmakers AMD and Intel (INTC) is difficult solely based on ATR. AMD, trading at \$98 with an ATR(10) of 4.17, appears significantly more volatile than INTC at \$36 with an ATR(10) of 1.45.

To address this issue and enable direct comparison of volatility across stocks, we can calculate the Normalized Average True Range (NATR). This metric expresses ATR as a percentage of the stock’s closing price.

NATR = (ATR / Closing Price) x 100

Let us continue with the AMD and INTC example, and calculate their respective NATR values

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

As you can see, despite the difference in trading prices, both AMD and INTC exhibit similar NATR values, indicating comparable volatility levels. This makes sense considering they operate in the same industry.

## Normalized ATR Google Sheets Formula

To make the NATR calculation consistent with the period used for the ATR, the ATR value is divided by yesterday’s close price and then multiplied by 100 to get the percentage value.

B1 = “Stock Code”

NATR = ( ATR / GOOGLEFINANCE( B1, “closeyest” ) ) * 100 or format as percentage

``=(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``