SHAPIRO

Overview

The SHAPIRO function performs the Shapiro-Wilk test for normality, which tests the null hypothesis that the data was drawn from a normal distribution. This test is commonly used to assess whether a dataset is normally distributed, which is an important assumption for many statistical analyses. The test returns a test statistic and a p-value, where a low p-value suggests that the data is not normally distributed. For more details, see the scipy.stats.shapiro documentation .

This example function is provided as-is without any representation of accuracy.

Usage

To use the function in Excel:


=SHAPIRO(data)

data (2D list, required): Sample data. Must contain at least three elements (rows).

The function returns a 2D list with two elements: the test statistic and the p-value (both floats), or an error message (string) if the input is invalid.

Examples

Example 1: Normal Data

Inputs:

data
1.2
2.3
1.8
2.1
1.7

Excel formula:


=SHAPIRO({1.2;2.3;1.8;2.1;1.7})

Expected output:

Statistic	P-value
0.9675	0.8591

Example 2: Non-Normal Data (Bimodal)

Inputs:

data
1.0
5.0
1.1
5.1
1.2
5.2

Excel formula:


=SHAPIRO({1.0;5.0;1.1;5.1;1.2;5.2})

Expected output:

Statistic	P-value
0.7194	0.0098

Example 3: Small Sample

Inputs:

data
2.0
2.1
2.2

Excel formula:


=SHAPIRO({2.0;2.1;2.2})

Expected output:

Statistic	P-value
1.0	1.0

Example 4: Uniform Data

Inputs:

data
0.1
0.4
0.7
1.0
1.3
1.6

Excel formula:


=SHAPIRO({0.1;0.4;0.7;1.0;1.3;1.6})

Expected output:

Statistic	P-value
0.9819	0.9606

Python Code


from scipy.stats import shapiro as scipy_shapiro
 
def shapiro(data):
    """
    Perform the Shapiro-Wilk test for normality.
 
    Args:
        data: 2D list. Sample data (must contain at least three elements).
 
    Returns:
        2D list: [[statistic, p-value]], or an error message (str) if input is invalid.
 
    This example function is provided as-is without any representation of accuracy.
    """
    # Validate input
    if not isinstance(data, list) or len(data) < 3:
        return "Invalid input: data must be a 2D list with at least three rows."
    # Flatten 2D list to 1D
    try:
        flat = []
        for row in data:
            if isinstance(row, list):
                flat.extend(row)
            else:
                flat.append(row)
        flat = [float(x) for x in flat]
    except Exception:
        return "Invalid input: data must contain numeric values."
    if len(flat) < 3:
        return "Invalid input: data must contain at least three numeric values."
    try:
        stat, p = scipy_shapiro(flat)
    except Exception as e:
        return f"scipy.stats.shapiro error: {e}"
    # Check for nan/inf
    if not (isinstance(stat, float) and isinstance(p, float)):
        return "Error: Output is not float."
    if stat != stat or p != p or abs(stat) == float('inf') or abs(p) == float('inf'):
        return "Error: Output is nan or inf."
    return [[stat, p]]

Example Workbook

Link to Workbook