Relative Strength Index (RSI) Formula in Google Sheets

Overview

The Relative Strength Index (RSI) is a momentum oscillator that measures the speed and change of price movements. It is used to identify overbought or oversold conditions in a market.

RSI Formula

The RSI formula is calculated using the following steps:

1. Calculate the Average Gain and Average Loss:

   • Average Gain (AG) = (Sum of Gains over the n periods) / n
   • Average Loss (AL) = (Sum of Losses over the n periods) / n

2. Calculate the Relative Strength (RS):

   • RS = Average Gain / Average Loss

3. Calculate the RSI:

   • RSI = 100 - (100 / (1 + RS))

In Google Sheets, you can implement the RSI calculation as follows:

Google Sheets Formula for RSI (14-period)

Assuming that your closing prices are in column A (from A2 to A15 for a 14-period RSI):

=100 - (100 / (1 + (AVERAGE(FILTER(A2:A15 - A1:A14, A2:A15 > A1:A14)) / AVERAGE(FILTER(A1:A14 - A2:A15, A2:A15 < A1:A14)))))

Explanation of the Formula

• FILTER function: This retrieves the gains and losses over a specified period by comparing current closing prices with previous ones.
• AVERAGE function: This then computes the average gain and loss over the defined period (14 periods in this case).
• RS: This is calculated by dividing the average gain by the average loss.
• Finally, the RSI is calculated using the formula that converts RS into a percentage scale.

Practical Example

Assuming the following closing prices are in cells A2 through A15:

You can paste the formula into cell B15 to calculate the RSI for these closing prices.

Important Notes

• The typical look-back period for RSI is 14 days but can be adjusted based on user preferences.
• It is essential to ensure that you collect enough data points that include both gains and losses for accurate results.

The above approach provides a comprehensive and efficient way to calculate the RSI within Google Sheets, facilitating further market analysis and decision-making. For an in-depth understanding of such analyses, consider exploring topics available on the Enterprise DNA Platform.