RANDOM_CORRELATION

Overview

The RANDOM_CORRELATION function generates a random correlation matrix with specified eigenvalues. This is useful in statistics and multivariate analysis for simulating correlation structures with desired properties, such as in Monte Carlo studies or testing algorithms that require valid correlation matrices. The function wraps the scipy.stats.random_correlation  method, which constructs a random correlation matrix using the specified eigenvalues. The eigenvalues must be non-negative and sum to the number of eigenvalues (i.e., the matrix dimension).

The Excel wrapper simplifies the function by exposing only the eigenvalues parameter. Parameters related to random seed, numerical tolerances, and random state are omitted, as they are not relevant or controllable in Excel. The output is always a single random correlation matrix for the given eigenvalues, and results may differ from repeated calls due to the random nature of the method.

The underlying equation is based on generating a valid correlation matrix $C$ with eigenvalues $\lambda_1, \lambda_2, \ldots, \lambda_n$ such that:

where $Q$ is an orthogonal matrix and $\Lambda$ is a diagonal matrix of the specified eigenvalues.

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

Usage

To use the function in Excel:

=RANDOM_CORRELATION(eigenvalues)

• eigenvalues (2D list, required): Table with one column and at least two rows, where each row is a non-negative float. The sum of all values must equal the number of rows.

The function returns a 2D array (matrix) of floats representing the random correlation matrix, or an error message (string) if the input is invalid. Each output matrix is symmetric and has ones on the diagonal.

Examples

Example 1: Basic Case (2x2 Identity Eigenvalues)

Inputs:

Excel formula:

=RANDOM_CORRELATION({1.0;1.0})

Expected output:

Example 2: Three Eigenvalues (3x3 Identity)

Inputs:

Excel formula:

=RANDOM_CORRELATION({1.0;1.0;1.0})

Expected output:

Example 3: Non-Identity Eigenvalues

Inputs:

Excel formula:

=RANDOM_CORRELATION({1.5;0.5})

Expected output:

Example 4: Float Eigenvalues

Inputs:

Excel formula:

=RANDOM_CORRELATION({0.7;1.3})

Expected output:

Python Code

from scipy.stats import random_correlation as scipy_random_correlation
from typing import List, Union
 
def random_correlation(eigenvalues: Union[List[List[float]], float, None]) -> Union[List[List[float]], str]:
    """
    Generates a random correlation matrix with specified eigenvalues.
 
    Args:
        eigenvalues: 2D list of float values, or a scalar. Eigenvalues of the correlation matrix. All values must be non-negative and sum to the number of eigenvalues.
 
    Returns:
        2D list representing the random correlation matrix, or an error message (str) if input is invalid.
 
    This example function is provided as-is without any representation of accuracy.
    """
    # Wrap scalar input as 2D list
    if isinstance(eigenvalues, (int, float)):
        eigenvalues = [[float(eigenvalues)]]
    if not isinstance(eigenvalues, list) or len(eigenvalues) < 2:
        return "Invalid input: eigenvalues must be a 2D list with at least two rows."
    # Flatten 2D list to 1D list
    try:
        flat_eigs = [float(row[0]) for row in eigenvalues]
    except Exception:
        return "Invalid input: eigenvalues must be a 2D list of floats."
    n = len(flat_eigs)
    if n < 2:
        return "Invalid input: eigenvalues must be a 2D list with at least two rows."
    if any(e < 0 for e in flat_eigs):
        return "Invalid input: all eigenvalues must be non-negative."
    if not abs(sum(flat_eigs) - n) < 1e-8:
        return "Invalid input: sum of eigenvalues must equal the number of eigenvalues."
    try:
        mat = scipy_random_correlation(flat_eigs).rvs()
    except Exception as e:
        return f"scipy.stats.random_correlation error: {e}"
    # Convert numpy array to 2D list of floats
    result = [[float(x) for x in row] for row in mat.tolist()]
    return result
 

Example Workbook

Link to Workbook