How to Calculate the Williams %R Using Excel

The Williams %R is a technical indicator developed by Larry Williams. It is an oscillator that is used by traders to identify underlying market strength. In this article I show how to calculate the %R using Excel.

Williams Percent Range

Williams %R on the FTSE 100 Index

The %R is a fast moving oscillator similar to the stochastic oscillator. It calculates how the current price compares to the recent highs and lows.

It does not have any smoothing which means that it responds quickly to a change in the price action. The indicator moves in a range from 0 to -100.

One of the best ways to trade using the %R is to identify divergences between price and the oscillator. If the price is setting new highs and the oscillator is not making new highs this is a divergence.

In the chart above the FTSE 100 index was making new highs during April 2015. However, the %R was getting weaker. By the end of April the price was following the %R lower.

How to Calculate the Williams %R

Excel is a great tool for calculating technical indicators, backtesting and developing trading strategies. On this website there are lots of resources that make it easy to test your own strategies using Excel.

Calculate Williams %R Using ExcelTo calculate the %R using Excel we start with the historical data. We then need 3 new columns to contain the recent highs, recent lows and the %R.

To calculate the highs we use the Excel function =MAX()

To calculate the lows we use =MIN()

The formula for %R is (high-close)/(high-low)*-100

In Excel the formulas are:

G15 =MAX(D2:D15)


If you need some more guidance, I have a video demonstrating how to set up the spreadsheet to calculate %R:

Larry Williams

Larry Williams has been a trader and teacher of traders for many years. One of his market philosophies is to “always think that the next trade will be a loser … a big loser”. It that sounds a bit strange to you then check out his book: Long-Term Secrets to Short-Term Trading.

More Resources

If you are interested in calculating technical indicators you might want to check out my page on How to Calculate the MACD Indicator or How to Calculate SuperTrend Indicator.

If you are developing your own trading strategies see my guide about How to Use a Tradinformed Backtest Model.

%d bloggers like this: