Table of Contents
R-Squared, also known as the coefficient of determination, is a statistical measure used to determine how well a regression line fits a set of data points. In Google Sheets, the R-Squared value can be calculated by first creating a scatter plot of the data and then using the “TREND” function to generate the regression line equation. The R-Squared value is then obtained by squaring the correlation coefficient (r) from the regression line equation. This will provide a value between 0 and 1, with a higher value indicating a better fit of the regression line to the data points. This measure can be useful in analyzing the strength of a relationship between two variables in a data set.
Calculate R-Squared in Google Sheets
R-squared, often written as r2, is a measure of how well a fits a dataset.
In technical terms, it is the proportion of the variance in the response variable that can be explained by the predictor variable.
The value for r2 can range from 0 to 1:
- A value of 0 indicates that the response variable cannot be explained by the predictor variable at all.
- A value of 1 indicates that the response variable can be perfectly explained without error by the predictor variable.
The following example shows how to calculate R-squared for two variables in Google Sheets.
Example: Calculating R-Squared in Google Sheets
Suppose we have the following data for the number of hours studied and the exam score received for 20 students:
Now suppose we want to fit a , using “hours” as the predictor variable and “score” as the response variable.
To find the R-squared for this model, we can use the RSQ() function in Google Sheets, which uses the following syntax:
=RSQ(known_ys, known_xs)
where:
- known_ys: the values for the response variable
- known_xs: the values for the predictor variable
In our example, we can type the following formula into cell D2:
=RSQ(A2:A21, B2:B21)
The following screenshot shows how to use this formula in practice:
This means that 72.73% of the variation in the exam scores can be explained by the number of hours studied.
Related:
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How do I calculate R-Squared in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-calculate-r-squared-in-google-sheets/
stats writer. "How do I calculate R-Squared in Google Sheets?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-calculate-r-squared-in-google-sheets/.
stats writer. "How do I calculate R-Squared in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-calculate-r-squared-in-google-sheets/.
stats writer (2024) 'How do I calculate R-Squared in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-calculate-r-squared-in-google-sheets/.
[1] stats writer, "How do I calculate R-Squared in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I calculate R-Squared in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.