L_DIFF

=L_DIFF(x, [n])

Description

L_DIFF returns the difference between adjacent values in a column vector: \({\Delta}x = x_{i+1}-x_i\). This can be accomplished with the DROP function in Excel like this:

L_DIFF(x) = DROP(x,1)-DROP(x,-1)

=LET(
    x, {1; 2; 4; 7; 11; 16},
    L_DIFF(x)
)

Result: {1; 2; 3; 4; 5}

The x parameter can be a single column vector, or an array of column vectors (of the same size). For example, if we have a table consisting of a column of x values and a column of y values, L_DIFF(table) would return Δx as the first column and Δy as the second column. The difference operator works separately on each column.

The function returns an error if the number of rows is equal to 1, so use TRANSPOSE(L_DIFF(TRANSPOSE(x))) if you want to apply L_DIFF to a row vector.

For approximating numerical derivatives, L_DIFF is meant to represent the difference operator Δ applied to discrete values using a forward difference, or \(x_{i+1}-x_i\). The resulting array has one less row, meaning that if x has m rows, then L_DIFF(x) will have m-1 rows.

Even though the difference operator has application to approximating derivatives, note that L_DIFF itself is not a derivative. Instead, a forward difference derivative would be Δy/Δx, or L_DIFF(vector_y)/L_DIFF(vector_x).

Calculating the n-th Difference

Specifying a value of n (valid only for n being an integer >= 1) will cause L_DIFF(x) to be run sequentially n times (the array loses 1 row each time). For example, L_DIFF(x,2) is the same as L_DIFF(L_DIFF(x)). Although this can be used in procedures for estimating higher-order derivatives, there may be better methods for estimating derivatives at specific values of x.

Both Matlab and Python NumPy have a similar function named diff, which is the reason for choosing this name for the LAMBDA Library.

Lambda Formula

This code for using L_DIFF in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

L_DIFF = LAMBDA(x,[n],
LET(doc,"https://www.vertex42.com/lambda/diff.html",
    n,IF(ISBLANK(n),1,n),
    REDUCE(1,SEQUENCE(n),LAMBDA(acc,i,
        IF(i=1,
            DROP(x,1)-DROP(x,-1),
            DROP(acc,1)-DROP(acc,-1)
        )
    ))
));

Name: L_DIFF
Description: Calculate the difference between adjacent values of a column vector
Arguments: x, n
Function:

=LET(doc,"https://www.vertex42.com/lambda/diff.html",
    n,IF(ISBLANK(n),1,n),
    REDUCE(1,SEQUENCE(n),LAMBDA(acc,i,
        IF(i=1,
            ARRAYFORMULA(L_DROP(x,1,0)-L_DROP(x,-1,0)),
            ARRAYFORMULA(L_DROP(acc,1,0)-L_DROP(acc,-1,0))
        )
    ))
)

L_DIFF Examples

Test: Copy and Paste this LET function into a cell
=LET(
    seq, L_LINSPACE(1,10,6),
    UNIQUE(L_DIFF(seq))
)

Result: {1.800000000000000;1.800000000000000}

=LET(
    seq, L_LINSPACE(1,10,6),
    L_DIFF(seq,2)
)

Result: {0; 0; 0; 8.88178E-16}

LET(
    seq, {1; 2.8; 4.6; 6.4; 8.2; 10},
    L_DIFF(seq,2)
)

Result: {0; 8.88178E-16; -1.77636E-15; 1.77636E-15}

Test: Copy and Paste this LET function into a cell
=LET(
    fib, DROP({0;1;1;2;3;5;8;13;21;34},1),
    L_DIFF(fib)
)

Result: {0,1,1,2,3,5,8,13,21}