PlanMaker PEARSON: Signed Correlation and Nonlinear Counterexamples
Use PlanMaker PEARSON with reproducible worksheets, positive and negative endpoint examples and a zero-correlation nonlinear pattern.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
PEARSON returns a signed correlation coefficient between −1 and 1. It describes linear association: a positive value means the two measurements tend to rise together, while a negative value means one tends to fall as the other rises.
Availability and version history
- Introduced: Documented by the PlanMaker 2010 manual; the first introduction release is not established.
- Availability: Documented in PlanMaker 2026; this is not a final working-version endpoint.
- Scope: The reference belongs to the 2026/NX manual family. Individual FreeOffice and mobile-build availability remains unverified.
- Checked: October 14, 2026, against the official function reference. Examples use vendor results and independent calculations; no native PlanMaker test was performed.
Syntax
=PEARSON(Range1, Range2)
The two required arguments are cell references to numeric data sets. The official entry says that empty cells, text and logical values are ignored. Different numbers of values produce #N/A.
Use complete paired observations in aligned rows. The entry does not specify all details of alignment when the two ranges have different patterns of nonnumeric cells. A coefficient should not be used to hide a data-cleaning decision.
Formulas use English names and comma separators. If your setup uses another separator, change the punctuation consistently.
Example: reconstruct a correlation coefficient
Create MeasuredPairs with the following values in A1:B6. Reserve C:G for calculations and I for summaries.
| Row | A: Input | B: Response |
|---|---|---|
| 2 | 1 | 2 |
| 3 | 2 | 4 |
| 4 | 3 | 5 |
| 5 | 4 | 4 |
| 6 | 5 | 5 |
Enter =PEARSON(A2:A6,B2:B6) in I2. The expected value is approximately 0.774596669.
Put =AVERAGE(A2:A6) in I4 and =AVERAGE(B2:B6) in I5. These return 3 and 4. Enter headers dx, dy, dx×dy, dx², dy² in C1:G1. In C2:G2 enter:
=A2-$I$4
=B2-$I$5
=C2*D2
=C2^2
=D2^2
Fill C2:G2 through row 6.
| Row | dx | dy | dx×dy | dx² | dy² |
|---|---|---|---|---|---|
| 2 | -2 | -2 | 4 | 4 | 4 |
| 3 | -1 | 0 | 0 | 1 | 0 |
| 4 | 0 | 1 | 0 | 0 | 1 |
| 5 | 1 | 0 | 0 | 1 | 0 |
| 6 | 2 | 1 | 2 | 4 | 1 |
The totals are 6, 10 and 6 for columns E, F and G. In I7 enter =SUM(E2:E6)/SQRT(SUM(F2:F6)*SUM(G2:G6)). This gives 6/SQRT(60), agreeing with I2. In I8 enter =I2^2; the result is 0.6, the corresponding squared correlation.
Changing scale does not create a stronger relationship
Add Transformed response in K1 and Negative response in L1. Enter =100+10*B2 in K2 and =-B2 in L2. Fill both through row 6.
| Result cell | Formula | Expected result |
|---|---|---|
| N2 | =PEARSON(A2:A6,K2:K6) |
0.774596669 |
| N3 | =PEARSON(A2:A6,L2:L6) |
-0.774596669 |
| N4 | =PEARSON(B2:B6,A2:A6) |
0.774596669 |
| N5 | =CORREL(A2:A6,B2:B6) |
0.774596669 |
Displayed results are rounded. Adding a constant and applying a positive scale leave this coefficient unchanged. Multiplying one series by −1 changes its sign. These are mathematical checks for the complete numeric fixture, not claims about every mixed-type edge case of the two functions.
Zero correlation can conceal a clear curve
Create another worksheet named CurvedPairs. Enter −2, −1, 0, 1, 2 in A2:A6, with x in A1. Put x squared in B1; enter =A2^2 in B2 and fill to B6, producing 4, 1, 0, 1, 4.
In D2 enter =PEARSON(A2:A6,B2:B6). The expected result is 0. Yet every response is exactly the square of its input. The positive and negative cross-products cancel; a zero linear correlation does not mean there is no relationship. Plotting the pairs exposes the curve that the single coefficient misses.
Vendor endpoint examples
On a separate worksheet named VendorPearson, put 1, 2, 3 in A1:A3; 2, 3, 4 in B1:B3; and 4, 3, 2 in C1:C3.
- D1:
=PEARSON(A1:A3,B1:B3)returns 1. - D2:
=PEARSON(A1:A3,C1:C3)returns −1.
These reproduce the two examples in the PlanMaker reference. Perfect correlation refers to a straight-line relationship, not to equality of the numbers.
Checks before interpreting the result
Inspect the paired rows, a scatter plot and the number of observations. Correlation alone does not establish causation, remove the influence of an unusual point, or determine whether a fitted line will predict new observations well.
A constant series has zero squared deviation, making the mathematical coefficient undefined. The cited entry does not name its native error token. Its explicit #N/A statement concerns mismatched value counts; do not generalize that token to every invalid input.