PlanMaker CORREL: Paired Data, Units and Worked Correlation Checks
Calculate PlanMaker correlation from complete paired observations, reconstruct the coefficient and check unit conversions and data alignment.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
CORREL measures the direction and strength of a linear association between two numeric data sets. Keep each observation’s two measurements on the same row: a correlation between hours and output only makes sense when the hours and output belong to the same observation.
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 and input rules
=CORREL(Range1, Range2)
Both arguments are required cell references. The PlanMaker reference says that empty cells, text and logical values are ignored, and unequal numbers of values produce #N/A. It does not fully describe how differently placed nonnumeric entries are aligned. For a reproducible analysis, prepare two complete numeric columns containing the same paired observations.
A coefficient near 1 indicates a positive linear association; one near −1 indicates a negative linear association. Zero means no linear correlation in the supplied data, although a nonlinear pattern can still exist. The coefficient is unitless and is not a percentage change or evidence that one variable causes the other.
Formulas below use English names and commas. Adapt separators to your PlanMaker configuration without changing the argument order.
Example: paired work hours and completed orders
Create a worksheet named WorkPairs. Enter this table in A1:B5, and keep C:G available for calculations.
| Row | A: Hours | B: Orders |
|---|---|---|
| 2 | 2 | 3 |
| 3 | 4 | 7 |
| 4 | 6 | 5 |
| 5 | 8 | 9 |
Enter =CORREL(A2:A5,B2:B5) in I2. The expected coefficient is 0.8: these four observations have a positive linear association, but do not lie exactly on a line.
Rebuild the calculation rather than treating the coefficient as a black box:
- Put
=AVERAGE(A2:A5)in I4 and=AVERAGE(B2:B5)in I5. The means are 5 and 6. - Enter headers Hours deviation, Orders deviation, Product, Hours squared, Orders squared in C1:G1.
- In C2:G2 enter
=A2-$I$4,=B2-$I$5,=C2*D2,=C2^2,=D2^2. Fill C2:G2 down through row 5.
| Row | C | D | E | F | G |
|---|---|---|---|---|---|
| 2 | -3 | -3 | 9 | 9 | 9 |
| 3 | -1 | 1 | -1 | 1 | 1 |
| 4 | 1 | -1 | -1 | 1 | 1 |
| 5 | 3 | 3 | 9 | 9 | 9 |
In I7 enter:
=SUM(E2:E5)/SQRT(SUM(F2:F5)*SUM(G2:G5))
The cross-product total is 16; both squared-deviation totals are 20. Thus 16/SQRT(20*20) gives 0.8, independently explaining I2. In I8, =PEARSON(A2:A5,B2:B5) gives the same coefficient for this complete numeric data set.
Unit changes, direction and pairing
On WorkPairs, put Minutes in K1 and Negative orders in L1. Enter =A2*60 in K2 and =-B2 in L2; fill both through row 5.
| Result cell | Formula | Expected result | Meaning |
|---|---|---|---|
| N2 | =CORREL(K2:K5,B2:B5) |
0.8 | Positive unit conversion preserves correlation |
| N3 | =CORREL(A2:A5,L2:L5) |
-0.8 | Reversing one measurement’s sign reverses direction |
| N4 | =CORREL(B2:B5,A2:A5) |
0.8 | Swapping the two complete series preserves correlation |
| N5 | =CORREL(A2:A3,B2:B3) |
1 | Two distinct pairs fit a line perfectly |
The last result is mathematically valid but weak evidence for a useful general relationship. More observations and a plot matter. Never sort just one input column: sorting must preserve whole observation rows.
Reproduce the vendor example
On a separate worksheet named VendorCorrelation, enter x values 2, 5, 3 in A1:A3 and y values 2, 7, 4 in B1:B3. Enter =CORREL(A1:A3,B1:B3) in D1. The independent value is approximately 0.997176465, which rounds to the manual’s 0.99718. Format more decimal places to inspect the result; formatting does not increase measurement accuracy.
Troubleshooting and limits
#N/A: The manual documents this for different numbers of values. Check both range endpoints and the underlying numeric data.- Constant series: If either series has no variation, the correlation formula has a zero denominator. The cited page does not specify the exact native error token for this case.
- Unexpected coefficient: Check row pairing, units, imported numeric text and unusual observations before changing the formula.
- Blank or mixed-type rows: The documented ignore rule is not a guarantee that misaligned data will be repaired. Build the complete paired input table explicitly.
The reference does not establish extreme-value numerical limits or every error-propagation case. These examples use ordinary finite numeric values.