ENG_WELCH

Welch’s method (also known as the periodogram averaging method) computes an estimate of the power spectral density by dividing the data into overlapping segments, computing a modified periodogram for each segment, and averaging these periodograms.

This method reduces noise in the estimated power spectra in exchange for reducing the frequency resolution. It is more robust than the standard periodogram for signals with significant noise.

Excel Usage

=ENG_WELCH(x, fs, window, nperseg, noverlap, nfft, spectral_detrend, return_onesided, spectral_scaling, avg_method)
  • x (list[list], required): Time series of measurement values (Excel range).
  • fs (float, optional, default: 1): Sampling frequency of the x time series.
  • window (str, optional, default: “hann”): Desired window to use.
  • nperseg (int, optional, default: null): Length of each segment.
  • noverlap (int, optional, default: null): Number of points to overlap between segments.
  • nfft (int, optional, default: null): Length of the FFT used.
  • spectral_detrend (str, optional, default: “constant”): Specifies how to detrend each segment.
  • return_onesided (bool, optional, default: true): If True, return a one-sided spectrum for real data.
  • spectral_scaling (str, optional, default: “density”): Selects between ‘density’ and ‘spectrum’.
  • avg_method (str, optional, default: “mean”): Method to use when averaging periodograms.

Returns (list[list]): A 2D array where the first row is frequencies and the second row is the power spectral density.

Example 1: Welch default (Hann window)

Inputs:

x
1 2 3 4 5 4 3 2 1 0 1 2 3 4 5 4 3 2 1

Excel formula:

=ENG_WELCH({1,2,3,4,5,4,3,2,1,0,1,2,3,4,5,4,3,2,1})

Expected output:

Result
0 0.0526316 0.105263 0.157895 0.210526 0.263158 0.315789 0.368421 0.421053 0.473684
0.37173 11.0677 27.5414 4.97818 0.0016883 0.323246 0.522246 0.0457904 0.00507076 0.185125
Example 2: Welch with median averaging

Inputs:

x avg_method nperseg
0 1 0 -1 0 1 0 -1 0 1 median 4

Excel formula:

=ENG_WELCH({0,1,0,-1,0,1,0,-1,0,1}, "median", 4)

Expected output:

Result
0 0.25 0.5
0 1.6 0
Example 3: Welch with spectrum scaling

Inputs:

x spectral_scaling nperseg noverlap
1 2 1 2 1 2 1 2 spectrum 4 2

Excel formula:

=ENG_WELCH({1,2,1,2,1,2,1,2}, "spectrum", 4, 2)

Expected output:

Result
0 0.25 0.5
0 0.125 0.25
Example 4: Welch with scalar-compatible input

Inputs:

x
4

Excel formula:

=ENG_WELCH(4)

Expected output:

Result
0
0

Python Code

Show Code 
import numpy as np
from scipy.signal import welch as scipy_welch

def eng_welch(x, fs=1, window='hann', nperseg=None, noverlap=None, nfft=None, spectral_detrend='constant', return_onesided=True, spectral_scaling='density', avg_method='mean'):
    """
    Estimate power spectral density using Welch's method.

    See: https://docs.scipy.org/doc/scipy/reference/generated/scipy.signal.welch.html

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

    Args:
        x (list[list]): Time series of measurement values (Excel range).
        fs (float, optional): Sampling frequency of the x time series. Default is 1.
        window (str, optional): Desired window to use. Default is 'hann'.
        nperseg (int, optional): Length of each segment. Default is None.
        noverlap (int, optional): Number of points to overlap between segments. Default is None.
        nfft (int, optional): Length of the FFT used. Default is None.
        spectral_detrend (str, optional): Specifies how to detrend each segment. Valid options: Constant, Linear, None. Default is 'constant'.
        return_onesided (bool, optional): If True, return a one-sided spectrum for real data. Default is True.
        spectral_scaling (str, optional): Selects between 'density' and 'spectrum'. Valid options: Density, Spectrum. Default is 'density'.
        avg_method (str, optional): Method to use when averaging periodograms. Valid options: Mean, Median. Default is 'mean'.

    Returns:
        list[list]: A 2D array where the first row is frequencies and the second row is the power spectral density.
    """
    try:
        def to_1d(v):
            if isinstance(v, list):
                if all(isinstance(row, list) for row in v):
                    return np.array([float(x) for row in v for x in row], dtype=float)
                return np.array([float(x) for x in v], dtype=float)
            return np.array([float(v)], dtype=float)

        x_arr = to_1d(x)

        detrend = spectral_detrend
        if detrend == "False":
            detrend = False

        f, pxx = scipy_welch(
            x_arr, 
            fs=float(fs), 
            window=window, 
            nperseg=int(nperseg) if nperseg is not None else None, 
            noverlap=int(noverlap) if noverlap is not None else None, 
            nfft=int(nfft) if nfft is not None else None, 
            detrend=detrend, 
            return_onesided=bool(return_onesided), 
              scaling=spectral_scaling,
            average=avg_method
        )

        return [f.tolist(), pxx.tolist()]
    except Exception as e:
        return f"Error: {str(e)}"

Online Calculator

Time series of measurement values (Excel range).
Sampling frequency of the x time series.
Desired window to use.
Length of each segment.
Number of points to overlap between segments.
Length of the FFT used.
Specifies how to detrend each segment.
If True, return a one-sided spectrum for real data.
Selects between 'density' and 'spectrum'.
Method to use when averaging periodograms.