Skip to main content
CCH Software User Documentation

Formulae - Differences from VAP

Product Help Banner.png

 

 

This section briefly describes formulae in Accounts Production and how they differ from VAP.

VAP Formula

AP Formula

=a,0001

Gets the current year balance (a,) from account 0001

=AP(“a,0001”)

Most AP commands need to be enclosed in AP(“”). This is to distinguish them from AP’s built-in Excel commands.

=#total,a,1,4

=SUM(A1:A4)

This is an AP built-in Excel command

=#cd62

The year end date, e.g. 2015, from Statutory Database.

=AP("#cd62")

i.e. effectively the same as VAP.

=RangeDef,0020,005Z,a,-1,0,ab

This RangeDef command lists the account descriptions of nonzero accounts between 0020 and 005Z. See Trust Accounts Help under Viztopia Formatter > Hints and Tips > Using the RangeDef command.

=AP("RangeDef,0020,005Z,a,-1,0,ab")

i.e. effectively the same as VAP. However note that:

=RangeDef,0020..005Z,,a,-1,0,ab

would also work in VAP, but not in AP.

=#Trustacs

This returns 1 if VAP is being used for a Trust Accounts client; otherwise 0.

=AP(“#Trustacs”)

i.e. effectively the same as VAP.

=a,6051+a,7051

Add up the balances on 6051 and 7051

=AP("(a,6051+a,7051)")

AP is generally better than VAP with brackets. But in this case, an extra set of brackets is needed to tell AP to process the expression as a formula (rather than as free text with formulae embedded in curly brackets).

Tip:  If a formula with no brackets doesn’t work, try adding a set of brackets.

=a,6051+7051

Add up balances on 6051 and 7051

=AP("(a,6051+a,7051)")

AP interprets the second nominal account, 7051, as the value 7051 unless it is preceded with "a,". 

=(Nz,a,8100..813Z>f0)

This command checks whether there are any nonzero (Nz) accounts between 8100 and 813Z. f0 means the number zero. The result is 0 if there are none or 1 if there are any.

=AP("(Nz,a,8100..813Z>f0)")

This is the same command but the result is TRUE or FALSE. A TRUE/FALSE result cannot be added up by a SUM() command. To get a 1 or 0 result this formula could be rewritten:

=IF(AP("Nz,a,8100..813Z"),1,0)

Here we use the built-in Excel command IF(). Also note that outside AP() commands 1 and 0 are written in the usual way and not as f1 and f0. This is discussed below under Accounts Production Pages.

=If (#Total,b,19,19,-1)<>f0 Then `nonzero` Else rs::`zero` Endif

=IF(B19,"nonzero",AP("rs")&"zero")

Here we test on a TRUE/FALSE result in B19. If TRUE we show the text “nonzero”. Otherwise we execute the AP command to suppress a row (rs) and show the text “zero”. This formula comes from the CCH Contents page.

=If Nz,ab,0020..005Z=f0 Then rs::`zero` Else `not zero` Endif

The row is suppressed (rs) if there are 0 nonzero accounts between 0020 and 005Z. This formula comes from column A of the CCH Income and Expenditure Account. If the row is shown, column A shows “not zero”; if suppressed “zero”.

=AP("If Nz,ab,0020..005Z=f0 Then RS `zero` Else `not zero` Endif")

i.e. effectively the same as VAP. This syntax is more common than the syntax above. Note that within an AP() command the back quotes (`) are still used and there is no double colon (::) used to join two commands together.

=sched,Add

This inserts an investment schedule from Trust Accounts.

=TA(“sched,Add”)

For the investment schedule the user must remember to use TA() rather than AP(). The other parameters of the sched command are the same as VAP.

sched,NoRows>f0

This is the page condition for the investment schedule. It means that the page will be printed if the number of rows (NoRows) of the schedule is greater than 0.

=TA("sched,NoRows")>0

In AP the sched command must be enclosed in a TA() command but other VAP expressions such as >f0 are not valid within a TA() command. So the test, >0, is outside the TA() command.

Another subtle difference is that page conditions in AP are not enclosed in an AP() command. It is assumed. So to tell AP that you don’t want to enclose the page condition in AP() you need to add a leading equals sign (=). Fortunately this complication only comes up with the investment schedule.

=#ld4000

Display the long description of account 4000. VAP could also display the short description.

=APNOMDESC("4000",0)

AP can only report the long account description. The syntax =AP(“#ld4000”) shows both the nominal code and the account description. So to get just the account description, an extra AP command has been added, APNOMDESC(). This example comes from the Property Income and Expenditure Account.

=@MAY15@,0001

VAP allowed a balance from a Saved Trial Balance to be referred to by name, MAY15 in this case.

=AP(“g,0001”)

In AP the balance can only be obtained from its identifying letter.

 

=#cd10'dst mmmm yyyy'

The DST date format showed 1st, 2nd etc.

=AP(“#cd10'd mmmm yyyy'”)

The DST format is not supported in AP.

=If (Pagestart,page9bi)=f0 Then

=If (note,note06)=f0 Then

This approach does not work in AP; page numbering commands such as Pagestart,page9bi and note numbering commands cannot be used as part of an If-condition. You usually need to repeat the page or note condition. See also Contents page.

 

Related

 

  • Was this article helpful?