How to Use the FISHER and FISHERINV Functions in Google Sheets for Correlation Hypothesis Testing

When calculating correlation coefficients between two variables using functions like CORREL or PEARSON in Google Sheets, the resulting Pearson r value is bounded strictly between -1.0 and +1.0. Because correlation coefficients are bounded, their sampling distribution is inherently non-normal and heavily skewed, particularly as the correlation approaches ±1.0. This non-normality poses significant challenges when attempting to construct valid confidence intervals or test statistical hypotheses comparing correlation strengths.

To overcome this limitation, statisticians utilise the Fisher z-transformation. Google Sheets provides two dedicated mathematical functions to automate this transformation and its reverse: FISHER and FISHERINV.

Understanding the Fisher Transformation

The Fisher transformation converts an observed correlation coefficient r into a normally distributed test statistic z using the inverse hyperbolic tangent function:

• FISHER(x): Transforms a correlation coefficient r into a z-score. Once transformed, the sampling distribution of z becomes approximately normal with a standard error dependent solely on sample size: SE = 1 / SQRT(N - 3). This allows analysts to calculate standard normal confidence intervals.
• FISHERINV(y): Converts a transformed Fisher z-score back into a standard correlation coefficient bounded between -1.0 and +1.0. This step is necessary after calculating confidence limits on the z-scale to interpret findings in terms of correlation.

Syntax and Argument Rules

Both functions accept a single numerical value, cell reference, or evaluated formula expression:

=FISHER(value)
=FISHERINV(value)

Adhere to the following mathematical constraints:

• Boundary Constraints for FISHER: The argument supplied to FISHER must strictly satisfy -1 < x < 1. Supplying values less than or equal to -1, or greater than or equal to 1, will return a #NUM! error because the natural logarithm of zero or negative numbers is undefined.
• Input Flexibility for FISHERINV: FISHERINV accepts any real numerical value from negative infinity to positive infinity, returning an output strictly within the range (-1, 1).
• Non-Numeric Inputs: If the referenced cell contains text or an empty string, both functions will return a #VALUE! error.

Mathematical Comparison: Input vs Transformed Output

The table below illustrates how the Fisher transformation scales non-linearly to stretch correlation coefficients approaching unity:

Pearson Correlation (r)	Transformed Fisher z [=FISHER(r)]	Reverse Transformed [=FISHERINV(z)]	Statistical Purpose
0.00	0.0000	0.0000	Zero correlation; baseline null hypothesis.
0.50	0.5493	0.5000	Moderate positive correlation; mild expansion.
0.80	1.0986	0.8000	Strong correlation; variance stabilization.
0.95	1.8318	0.9500	Very strong correlation; significant transformation expansion.
0.99	2.6467	0.9900	Extreme correlation; eliminates bounded skewness for z-testing.

Step-by-Step: Constructing a 95% Confidence Interval for Correlation

Suppose you calculate a Pearson correlation of r = 0.75 from a dataset of N = 50 participants, stored in cells B1 and B2 respectively:

1. In cell B3, calculate the Fisher z-transformation:

   =FISHER(B1)

   Result: approximately 0.9730.

2. In cell B4, calculate the standard error of the transformed statistic:

   =1 / SQRT(B2 - 3)

   Result: approximately 0.1459.

3. In cells B5 and B6, determine the lower and upper bounds on the z-scale at the 95% confidence level (using critical z-value 1.96):

   =B3 - (1.96 * B4)
   =B3 + (1.96 * B4)

   Lower z-bound: 0.6870; Upper z-bound: 1.2590.

4. In cells B7 and B8, convert the bounds back into Pearson correlation values using FISHERINV:

   =FISHERINV(B5)
   =FISHERINV(B6)

   Final 95% Confidence Interval for r: approximately [0.596, 0.851].

Frequently Asked Questions

Why can’t I just construct a confidence interval directly around r using standard deviation?

Because correlation coefficients cannot exceed 1.0, a standard confidence interval (such as 0.85 ± 0.20) would yield an impossible upper bound of 1.05. The Fisher transformation maps correlations onto an infinite scale, preventing out-of-bounds bounds when inverted.

Is the Fisher transformation valid for small sample sizes?

The transformation performs reliably for sample sizes N > 10. For sample sizes under 10, bootstrap resampling or non-parametric permutation methods are recommended.