What's the difference between RSQ and CORREL?

R² (from RSQ) equals correlation squared; it ranges 0–1 and focuses on prediction fit. CORREL ranges –1 to +1 and shows direction of relationship. Use RSQ to measure how well a model predicts; use CORREL to see strength and direction of association.

Can RSQ ever be negative?

No. RSQ always returns 0 to 1. Negative R² values, which theoretically indicate a model worse than a horizontal line, are typically converted to 0 in spreadsheets. If you see a negative result, check your data for errors.

How many data points do I need for RSQ to be meaningful?

RSQ requires at least 2 pairs to calculate, but results stabilize around 10+ pairs. With fewer than 5 points, R² can be misleading—a few outliers will dominate. Always verify results with a scatter plot.

Why is my R² very low even though I see a clear trend in the data?

Low R² despite visible correlation usually means a non-linear relationship (curved rather than straight), presence of outliers, or hidden subgroups (e.g., different neighborhoods behaving differently). RSQ only captures linear fit; verify your relationship is straight-line before trusting the result.

RSQ function

RSQ returns the coefficient of determination (R²), indicating how well a linear regression model fits your data on a scale from 0 (no fit) to 1 (perfect fit).

=RSQ(known_ys, known_xs)

Generate a RSQ formula

Describe what you need. The generator will reach for RSQ where RSQ is the right tool, and tell you when it is not.

Arguments

How RSQ reads its arguments

Argument	Required	Description
known_ys	Required	The dependent variable values (outcomes you're predicting); must be numeric and match known_xs in count. Empty or mismatched arrays return errors.
known_xs	Required	The independent variable values (predictors); must be numeric, same length as known_ys, and contain at least one value that differs from others.

Returns

A decimal number between 0 and 1 representing the goodness-of-fit percentage.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Assess how well bedroom count predicts list price

Beds	List Price
3	450000
4	625000
2	320000
5	850000
3	480000

=RSQ(D2:D6, B2:B6)

Result: 0.918

The R² of 0.918 means 91.8% of list-price variation is explained by bedroom count. This strong fit (near 1.0) shows that beds are a highly reliable predictor of price in this market. More bedrooms consistently correlate with higher asking prices.

2. Evaluate days on market as a price predictor

Days on Market	List Price
15	450000
8	625000
28	320000
5	850000
18	480000

=RSQ(D2:D6, E2:E6)

Result: 0.412

The R² of 0.412 shows only 41.2% of price variation is explained by days on market. This weak fit indicates time-on-market is a less reliable price predictor than bedrooms, possibly because unsold homes have complex reasons (overpriced, timing, or market conditions) beyond simple price correlation.

3. Determine if more bathrooms means homes sell faster

Bathrooms	Days on Market
2	15
3	8
1	28
4	5
2	18

=RSQ(E2:E6, C2:C6)

Result: 0.627

The R² of 0.627 indicates 62.7% of selling-speed variation relates to bathroom count. Homes with more bathrooms tend to sell faster, suggesting bath count moderately predicts market velocity. However, the moderate fit implies other factors (location, condition, price) also influence speed.

Common errors

Which RSQ error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The known_ys and known_xs arrays have different lengths, or one array contains non-numeric text or errors.	Verify both ranges have identical row counts using COUNTA; remove text, symbols, or error values; use IF to convert or filter invalid entries.
#NUM!	One array (typically known_xs) has zero variance—all values are identical, so no linear regression line can be calculated.	Check that your X values (or Y values) are not all the same. If all properties have 3 beds, add diverse properties or switch to a different analysis.
#N/A	One or both arrays are empty, reference a deleted range, or contain IFERROR/IFNA results that suppress values.	Ensure both ranges contain data; verify no filters hide rows; use COUNTA to confirm both arrays have at least 2 visible values.

Tips and when to use something else

R² ranges 0 to 1; values above 0.7 typically signal strong linear fit, 0.3–0.7 moderate, below 0.3 weak. Use AVERAGE or MEDIAN to summarize weak-fit data.
R² measures linear association only; it cannot detect curved relationships or prove causation. Correlation ≠ causation—more beds don't cause higher prices.
Use IF or IFERROR to exclude outliers and invalid data before analysis; a few extreme values can artificially inflate or deflate R².
Small datasets (under 5 pairs) produce unreliable R² values. For robust analysis, gather 10+ data points and verify results with visual scatter plots.

Related functions

AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.COUNTACOUNTA counts the number of non-empty cells in a range, regardless of their content type or whether they contain text, numbers, or errors.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.

Frequently asked questions

What's the difference between RSQ and CORREL?: R² (from RSQ) equals correlation squared; it ranges 0–1 and focuses on prediction fit. CORREL ranges –1 to +1 and shows direction of relationship. Use RSQ to measure how well a model predicts; use CORREL to see strength and direction of association.
Can RSQ ever be negative?: No. RSQ always returns 0 to 1. Negative R² values, which theoretically indicate a model worse than a horizontal line, are typically converted to 0 in spreadsheets. If you see a negative result, check your data for errors.
How many data points do I need for RSQ to be meaningful?: RSQ requires at least 2 pairs to calculate, but results stabilize around 10+ pairs. With fewer than 5 points, R² can be misleading—a few outliers will dominate. Always verify results with a scatter plot.
Why is my R² very low even though I see a clear trend in the data?: Low R² despite visible correlation usually means a non-linear relationship (curved rather than straight), presence of outliers, or hidden subgroups (e.g., different neighborhoods behaving differently). RSQ only captures linear fit; verify your relationship is straight-line before trusting the result.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17