Zoho Sheet PEARSON Function: Measure Linear Correlation

PEARSON(x; y)

The official reference lists no optional parameters or defaults. It describes text, logical values and empty cells as ignored. Zoho also explicitly associates #N/A! with differing x/y counts. Unresolved detail: The reference does not clearly specify every pairwise treatment when only one column has a missing or text value in a given row; test mixed and nonaligned observations natively before documenting a universal pairing rule. The clean worked dataset below contains only paired numeric entries. Zoho PEARSON, checked 2026-10-12.

Create a new worksheet for the example and enter these six observations. The A column is the independent variable (week number); B contains the dependent variable (orders). Enter numbers, not quoted text.

This is an illustrative synthetic dataset, not downloaded Zoho Sheet output or a claim about actual business demand. The arithmetic below has been independently checked against these values. Formulas use the semicolon separators shown in Zoho’s English-language function help. If your spreadsheet’s formula parser requires another separator, check the workbook’s locale/settings rather than copying translated notation blindly.

1. Type the six paired observations into A1:B7. Ensure each week and its order count share a row.

2. In a free cell, enter:

   =PEARSON(A2:A7;B2:B7)
   

3. Independently calculated expected result: 0.911309664 (rounding/display may vary). This is a strong positive sample linear association for these synthetic observations.

4. If you display only three decimal places, the value appears as 0.911; changing number formatting does not change its underlying calculated precision.

To see the calculation rather than accepting it as a black box, compute means x̄ = 3.5 and ȳ = 6.166666667, and sums:

Σ(x−x̄)(y−ȳ) = 22.5
Σ(x−x̄)² = 17.5
Σ(y−ȳ)² = 34.833333333
r = 22.5 / SQRT(17.5 × 34.833333333)
  = 0.911309664

These exact intermediate values independently reproduce the expected result; they were not copied from a live Zoho calculation.

Keep weeks in A2:A7. Enter the following hypothetical decreasing-series values in C2:C7, one per corresponding week: 10, 8, 9, 5, 6, 3. (They are 13 minus the original B-column values.) Then evaluate:

=PEARSON(A2:A7;C2:C7)

Expected result: −0.911309664. It is just as strong a linear association in magnitude as Example 1, but in the opposite direction. If you square both correlations, each produces 0.830485304; an r² value alone hides direction. This matters when evaluating relationships such as response time versus satisfaction, rather than simply week number versus demand.

When considering a simple straight-line forecast, check more than the sign of r. Zoho documents an RSQ function, whose output is the square of the Pearson coefficient. With the original A2:A7 and B2:B7 data, the following formulas are related:

=PEARSON(A2:A7;B2:B7)
=PEARSON(A2:A7;B2:B7)^2
=RSQ(A2:A7;B2:B7)

The mathematical expectations are 0.911309664, 0.830485304, and 0.830485304. That means a fitted simple linear trend explains about 83.05% of sample y-variation under this fitted model, not that it predicts future weeks with 83.05% probability. Zoho’s official RSQ reference uses RSQ(x; y); Microsoft documentation for some correlation-related functions may name data arrays differently. Always label which column represents the explanatory variable when interpreting the results. Zoho RSQ, checked 2026-10-12.

A practical quality-control dashboard might include a scatterplot, PEARSON, RSQ, and a sample-size cell such as =COUNT(A2:A7). Do not use an arbitrary correlation cutoff as proof of causation or a guaranteed forecasting result. Correlations may reflect common trends, confounding factors, outliers or chance, especially with six observations. As a data-quality habit, inspect rows that contain blanks, text, numbers stored as text or errors before computing statistics; the vendor reference’s generic ‘ignored’ wording does not fully specify pairing behaviour across incomplete rows.

Avoid using PEARSON to establish whether an intervention causes improvement. A data pair recorded over time can be driven by the passage of time itself. Also consider whether observations are independent, whether measurement scales make sense, and whether one unusual week is unduly influential.

Zoho documents PEARSON(x; y) and an RSQ(x; y) companion; both are currently listed in the dated official statistical catalogue. Microsoft Excel documents PEARSON(array1, array2) and explicitly states that its referenced arrays ignore text, logical values and blank cells while zeros count. The displayed separator punctuation differs across official English examples (; in Zoho, , in Excel). Do not infer that differences in incomplete-pair treatment, error text or platform availability are absent. Zoho PEARSON and Microsoft PEARSON, checked 2026-10-12.

Alternative questions require different measurements. CORREL is Zoho’s separate documented correlation function; RSQ removes the sign by squaring; SLOPE expresses y-change per x-unit rather than a unitless coefficient; FISHER is a different transformation for a correlation coefficient, not the raw r itself. All these vendor help links were checked 2026-10-12.

No claim is made that all Google Sheets, desktop editions, historical Zoho releases, mobile apps or account tiers behave identically. The official function help provides a general Zoho Sheet syntax but does not supply a supported-first-version table or a specific per-plan availability matrix. Workbook locale controls can affect formatting and input interpretation; Zoho’s locale documentation is the appropriate reference for changing sheet settings (checked 2026-10-12).

• Official documentation checked: Zoho PEARSON help, 2026-10-12; syntax, ignored value types and explicitly listed errors.
• Related official documentation checked: Zoho RSQ, Zoho CORREL, Zoho SLOPE and Microsoft PEARSON, 2026-10-12.
• Independent calculation: The six-row coefficient, its sign reversal and its square were arithmetically checked. All are expected outputs, not native screenshots.
• Native Zoho Sheet execution: NOT RUN. The behaviour of incomplete pairs, imported text, constant arrays, locales and edition-specific clients remains untested.
• Images: No genuine Zoho Sheet screenshots are included; no mock application images are presented.