DIFF Function

DIFF calculates the difference between succeeding numbers in a range or array.
The calculation can be done recursively as many times as desired (Order).

DIFF returns an array of differences which may be shorter than the set of values from the input range or array. The number of terms returned is dependent on the chosen values of the DIFF parameters: for example, with a lag of 1 and an order of 1 and a total of 10 numbers 9 differences will be returned.

Options are provided to control the interval between the items to be differenced (Lag) and the number of times the difference calculation will be done on the input data (Order).

The initial value for DIFF can either be the start value in the range or array (or the last value with negative Lag), or a separately supplied Start Value.

The differences returned may optionally be aligned with the corresponding positive terms in the difference. In this case the values returned will be padded with using the value of the Align parameter to match the number of values in the input Range_Array.

DIFF is a non-volatile multi-threaded array function.

DIFF Syntax

DIFF (Range_Array [,StartValue] [,Lag] [,Order] [,Align] [,Down]  )

Range_Array

The Values to be DIFFed. Can be a Range or an Array, a Column or a Row or a range of rows and columns. If any non-numeric or empty values are found in Range-Array DIFF will return #Value.

StartValue (Optional.)

If the StartValue parameter is supplied then it’s value will be used as the first value of the series of values for positive Lag or as the last value in the series for negative Lag.

If StartValue is not supplied the first or last value (depending on the sign of Lag) in Range_Array will be used as the start value.

If Range_Array is a 2-dimensional set of values then StartValue must be a corresponding single row or column of start values.

Lag (Optional. Default 1)

A positive or negative integer giving the interval between the values to be DIFFed.

Positive values for Lag give differences going forward.
A Value of 1 returns the second value minus the first, the third value minus the second etc. A Value of 2 returns the third value minus the first, the fourth value minus the second etc.

Negative values for Lag give differences going backwards.
A Value of -1 returns the first value minus the second, the second value minus the third etc. A value of -2 returns the first value minus the third, the second value minus the fourth etc.

Order (Optional. Default 1)

A positive integer giving the number of times the difference operation will be done.

A value of 2 is equivalent to DIFF(DIFF( ..)

Align (Optional. Default 0.0)

When Align is used DIFF will line up the difference results with the corresponding terms being differenced from in the input values.
With a Lag of +1 the result of the second value minus the first value will be in the second position.
With a Lag of -1 the result of the first value minus the second value will be in the first position.

When Align is set to FALSE the first calculated difference is always in the first position regardless of Lag, and the number of values in the output will be less than the count of values in Range_Array and StartValue.

The output will be padded as required at the start or end using whatever value is given for Align to ensure that the number of values in the output is the same as the number of values in the input.

Typical values for Align are 0.0 or “”.

Down (Optional.)

When Range_Array contains multiple rows and columns Down determines the direction of the difference calculation.

By default, if there are more rows than columns in Range_Array Down will be True, else False.

When specified Down overrides the default behaviour.

If Down is True then a separate set of differences will be calculated for each column in Range_Array.
If Down is False then a separate set of differences will be calculated for each row in Range_Array.

DIFF Examples

Difference of the Closing Balance with a Start Value of 100

In the image below:

Column J shows a DIFF of the data using all the defaults. The results appear starting in the second row. The default is Aligned =0.0 so the first result is 0.0 and the result of 9.0-1.0 = 8.0 is positioned in the same row as the 9.0

Column K shows a second-order difference of the data with Align=False. This is equivalent to DIFF(DIFF … ))

Column M shows an Aligned Diff padded out with “” – the differences are in the same row as the first term in the difference.

Column O shows an Aligned Diff with a Lag of 2. The first result is the third value minus the first value. NA is used as the Align value to make it easier to see where the unavailable results are.

Column Q shows a Diff with a Lag of minus 2 and Align=False. The first result is the first value minus the third value.

Differences, then differences of differences, then differences of differences of differences

Copyright © 2023 Decision Models Ltd