Calculating Range In Excel

Range is the difference between high and low in a particular time period, such as a trading day. The range formula is very simple:

Range = High – Low

In Excel, you simply take the cell containing the high and subtract the cell containing the low from it – see screenshot from ATR Calculator below.

Calculating Range in Excel - ATR Calculator

Obviously, unlike many technical indicators and other financial calculations, for calculating range you need the history of high and low, not close. Typically you need historical price data in the OHLC or HLC format.

Calculating Range in ATR Calculator

You can calculate average range in the ATR Calculator by selecting “Range” in the drop-down box in cells K4/L4/M4. You can also see the range for each bar in column G.

What Is ATR?

Average True Range (ATR) is a more advanced concept of range and more suitable in some types of markets compared to the traditional range. Here you can see a detailed explanation of true range and ATR calculation.