Using ranges within RangeDef and RangeCode formulae
Introduction - Ranges
Name ranges consist of one or many nominal accounts, or two or more name ranges, viewed from the File > Maintenance > Accounts > Name Range menu option, link: Name Ranges
A Name Range in a formula will always be preceeded with an asterisk * and presented within square brackets []. E.g. [*TOTREV] the name range for total revenue (turnover).
A nominal range will refer to a range of nominal codes e.g. 0001..049Z, being the range for total revenue (turnover).
Nominal codes within most chart of accounts in CCH Accounts Production use a 4-digit code (but not, for example the Academy chart which is a 3-digit chart). Examples here will use the common 4-digit chart.
Examples of Name Ranges are given within the introduction section link below, and one here for information =AP("[a,*TOTREV]")
link: Introduction to Formulae used in CCH Accounts Production
Within a formula a = this year, b = last year, c = the year before that and so on up to f.
Warning: Please be aware that the CCH Accounts Production Name Ranges should not be changed, you may create your own name ranges but it is not advisable to change the standard CCH Name Ranges.
Range Commands using Name Ranges
These allow the descriptions, balances and differences between a range of nominal accounts to be displayed using a single formula for each element to be included:
=AP(“RangeDef,[*AE],s,a,-1,0,ab”)
This command inserts a list of nominal descriptions (in this case based on a name range of Administration Expenses) on a number of rows based on the specified range and conditions applied. It is normally used in conjunction with the RangeCode formula to insert a listing of nominal account balances in a report.
The structure of the formula is explained below:
|
RangeDef or RangeCode |
The definitions (long description) of a range of nominal accounts |
|
,[*AE] |
The range of accounts to be included (the example refers to the global range of AE but can also refer to actual nominal codes, see below) |
|
,s |
The letter s here will bring through any subcodes, if you wish to show without subcodes, then remove the s but ensure the commas remain |
|
,a |
Required field. In RangeCode below, this refers to the period of the data to be included, a for current year or b for prior year. |
|
-1 |
-1 (True) or 0 (False) indicating if rows are to be inserted to take the data. If this is 0 then the data included would overwrite the following rows rather than new rows being inserted to accommodate the extra lines. |
|
0 |
-1 (True) or 0 (False) indicating if rows with zero values are to be included. If this is -1 then the test referred to next would be ignored. |
|
ab |
The periods to be scanned for balances on account. In this case will look at both this year and the prior year and if either has a balance will include it. If this was just "a" the test would only be done on the current year and just "b" on the prior year. |
=AP(“RangeCode,[*AE],s,a,-1,0,ab”)
The RangeCode command takes the same format as for the RangeDef and would normally appear in a column where the figures are to appear on the same line as the RangeDef command.
This command inserts a range of nominal values on a number of rows based on the specified range and the conditions applied, as described for RangeDef above.
To list all accounts that make up revenue (turnover) on a detailed report you would use the following:
|
=AP("RangeDef,[*TOTREV],s,a,-1,0,ab") |
=AP("RangeCode,[*TOTREV],s,a,-1,0,ab") |
Totalling a RangeCode column
When using a RangeCode command, to enter the sum of that range use the formula based on the name range, in our example above =AP("[a,*TOTREV]"). The Excel command =sum() does not recognise a RangeCode formula.
Range Commands using Nominal Account codes
In addition to using name ranges to present a list of nominal accounts, instead a range of nominal codes may be specified. This is achieved by replacing the name range e.g. [*TOTREV] in the above formula with a list of codes in () brackets. Apart from this the formula is the same.
The example below show the nominal range for the name range [*TOTREV] (Total Revenue) being replaced with nominal codes.
For the Nominal Descriptions, RangeDef
|
=AP(“RangeDef,[(0001..049Z)],s,a,-1,0,ab”) |
Inserts a listing of accounts in the range 0001 to 049Z where there is an active balance in either the current or prior year on an account. |
For the Nominal Balances, RangeCode
| To insert the balances for the current year next to each account | For the comparative year |
|
=AP("RangeDef,[(0001..049Z)],s,a,-1,0,ab") |
=AP("RangeDef,[(0001..049Z)],s,a,-1,0,ab") |
To total the range use =AP(“a,0001..049Z") for this year and =AP(“b,0010..049Z]") for the prior year.
Splitting a range
It is possible to split a range of nominal codes easily, but not a name range. To do so simply enter the formula on two consecutive lines. E.g. to omit codes 3681 to 3699, enter:
=AP(“RangeDef,[(3000..3680)],s,a,-1,0,ab”) =AP(“RangeCode,[(3000..3680)],s,a,-1,0,ab”)
=AP(“RangeDef,[(3700..399Z)],s,a,-1,0,ab”) =AP(“RangeCode,[(3700..399Z)],s,a,-1,0,ab”)
The formula to produce the total of these accounts would be =AP(“[a,(3000..3680)]+[a,(3700..399Z)]”)
RangeDefCode
This command will be used instead of RangeDef to return the nominal code together with the description. It is useful as an audit trail and used within the Formatted Trial balance and also the Consolidation entities. The example below refers to Trade receivables:
=AP("RangeDefCode,[*TRDEREC],S,a,-1,0,ab")
The RangeCode command stays the same
=AP("RangeCode,[*TRDEREC],S,a,-1,0,ab")