RangeDif and Dif commands
The RangeDif command is used in variance analysis to show the difference between two periods. It has similar parameters to the RangeDef command. The RangeDif command is generally used with the RangeDef and RangeCode commands. For instance, column B might use a RangeDef command to list the account descriptions, while column C lists the current year balances using a RangeCode command, column D lists last year's balances using another RangeCode command and column E shows the differences between current year and last year.
The Dif command is used for adding totals for the rows produced by the RangeDif command.
Syntax
There are 2 forms of the RangeDef command:
- Named range, e.g. =AP("RangeDif,[*TOTREV],a,b,0,ab")
This form prints shows the differences between the current and last year balances for accounts in the named range TOTREV which have a balance in either the current or last year.
- Complex nominal account range, e.g. =AP("RangeDif,[(7010..7090)],a,b,0,ab")
This form shows the differences between the current and last year balances for accounts between 7010 to 7090 which have a balance in either the current year or last year.
Parameters
- 1 Account range The first parameter specifies the accounts range. This is different for the three forms above.
- In the first form, the first parameter specifies the named range code. This must start "[*" and end "]".
- In the second form, the first parameter specifies the nominal range. This must start "[(" and end ")]". Only a simple code range is supported, i.e. pattern matching using the ~ separator is not supported.
- 2, 3 Year codes The second and third parameters say which balances to compare, i.e. “a” for the current year, “b” for comparatives and so on.
- 4 Zero balances The fourth parameter above (0) indicates whether zero balance rows should be included. Normally they are not. But occasionally users want to see all rows.
- 5 Years to check The fifth parameter indicates which balances are going to be printed on the same line. “ab” above indicates that commands are going to appear to print the current and comparative years. This is required so that the account description is suppressed only if all the periods have a zero balance.
- 6 Not in use (optional).
- 7 Include subaccounts (optional) Use "S" to include them or omit this parameter if not required.
- 8 Debit/credit only (optional) The eighth parameter is "deb" to list only differences between debit balances and "cred" to list only the differences between credit balances. ("Dr" and "Cr" work as well but are not recommended as they do not work on the Nz command). If "deb" or "cred" is used then the matching RangeDef and RangeCode commands should also use "deb" or "cred" for the differences to line up with the account descriptions and balances.
Notes
- If the cell is formatted as a percentage type then the percentage difference is returned.
- The Dif command has the same parameters as the RangeDif command, except that the Years to check parameter (parameter 5) is usually left out as it is not relevant. So to total =AP("RangeDif,[*TOTREV],a,b,0,ab") you might write =AP("Dif,[*TOTREV],a,b,0"). Note that the last parameter here, 0, is not relevant for a Dif command, but is mandatory because it is mandatory for the RangeDif command.