Nominal ranges

Further options available within ranges

When a nominal range is used, it can be specified as firstCode..lastCode. For instance, the formula =AP("a,0001..4999") adds up nominals from 0001 to 4999. However, it is also possible to specify firstCode~lastCode to add up all nominal codes between firstCode and lastCode that match the given pattern.

For instance, if nominal accounts are 4 digits and subaccounts are 2 digits:

• =AP("a,0100..0900") adds up the current year balance ("a") for all accounts from 0100 to 0900.
• =AP("a,0100~0900") adds up accounts 0100, 0200, 0300 up to 0900 but skips intermediate accounts. When the pattern matching operator (~) is used, the system constructs a pattern, in this case 0?00 where a ? is inserted when the character in the first code differs from the character in the same position in the second code. The codes that are added up are between the first code and the second code and match the pattern.
• =AP("a,310001..310909") adds up all subaccounts and main accounts between 3100/01 and 3109/09. Includes subaccounts /10 onwards for every code except 3109.
• =AP("a,310001~390001") adds up 01 subaccounts 310001, 320001 ... 390001 as the pattern used to match accounts is 3?0001. Other main accounts and other subaccounts are skipped.

These nominal ranges can be used to obtain the total of a nominal range as shown above. They can also be used in the Nz, RangeDef and RangeCode commands, e.g.

• =AP("a,310001~390001") adds up 01 subaccounts 310001, 320001 ... 390001
• =AP("Nz,ab,310001~390001") counts how many of the 01 subaccounts 310001, 320001 ... 390001 have a nonzero balance for either this year or last year ("ab"). This might be used in a row condition that determines how the balances are laid out, e.g. listed in column C and subtotalled to column D, or listed directly in column D.
• =AP("RangeDef,[(310001~390001)],s,a,-1,0,ab") lists the account descriptions for subaccounts 310001, 320001 ... 390001 that have a nonzero balance for either this year or last year ("ab"). Note that for a RangeDef and a RangeCode command [( and )] brackets are required to indicate a complex nominal range. Also the ",s" parameter is required to indicate that subaccounts are required.
• =AP("RangeCode,[(310001~390001)],s,a,-1,0,ab") lists the current year account balances for subaccounts 310001, 320001 ... 390001 that have a nonzero balance for either this year or last year

Note

The nominal range can also specify only credit or debit balances. The syntax is slightly different for an Nz, RangeDef or RangeCode command. But for a simple nominal range:

• =AP("cval,a,0100..0900") adds up the current year credit balances ("a") for all accounts from 0100 to 0900. Use dval instead for the debit balances