Using MS Excel Functions

Last modified 12:25, 3 Sep 2013

Overview

The spreadsheet used for editing formats supports standard MS Excel functions, for example =sum(), =average(), etc. It also supports an additional function for the extraction of data from the Accounts Production database, =AP(…), where … is the formula to execute. The formula within the AP() function currently follows the syntax of the formula used in VAP. 

Commonly used formulae and commands

The following are some of the most commonly used formulae.

Nominal Formulae

The following lists some simple formulae:

3 Digit Nominal

4 Digit Nominal

=AP(“#ld001”)

=AP(“#ld1001”)

=AP(“a,001”)

=AP(“a,1001”)

=AP(“a,001..099”)

=AP(“a,1001..1099”)

=AP(“a,001+002”)

=AP(“a,1001+1002”)

=AP(“{a,001-b,001}”)

=AP(“{a,1001-b,1001}”)

=AP(“{(a,001-b,001)/b,001}”)

=AP(“{(a,1001-b,1001)/b,1001}”)

Range Formula

Range names exist for every nominal account and can be viewed from the Maintenance > Accounts > Name Range menu option. Name Ranges are retrieved in formulae as follows:

=AP(“[a,*TO]”)

Turnover (current year).

=AP(“[b,*AE]”)

Administrative expenses (comparative year).

=AP(“[a,*AE]+[a,*DC]”)

Total of administrative and distribution expenses.

=AP(“[a,*TO]-[b,*TO]”)

Difference between current and comparative turnover.

Range Commands

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 if nominal account balances in a report.

The structure of the formula is explained below:

RangeDef

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

Required field.

,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 look at both this year and the prior year and if either has a balance include it. If this was just a the test would only be done on the current year and b on the prior year.

=AP(“RangeCode,[*AE],s,a,-1,0,ab”)

As can be seen, this command takes the same format as that 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 range def.

Example

To list all accounts that make up turnover on a detailed report you would use the following:

B

C

D

10

=AP("RangeDef,[*TO],s,a,-1,0,ab")

=AP("RangeCode,[*TO],s,a,-1,0,ab")

11

   

12

=SUM(C4:C5)

=SUM(D4:D5)

13

   

As lines are inserted the sum range will be increased. Alternatively you could have created the total by referring to the total of the global range using =AP(“[a,*TO]) and =AP(“[b,*TO]).


Range Commands using Nominal Accounts

In addition to using name ranges to insert a list of nominal accounts, you can also specify a range of nominal codes. This is achieved by replacing the *RANGE_CODE in the above format with a list of codes in brackets. Apart from this the format is the same.

For the Nominal Descriptions

3 Digit Nominal Ledger

4 Digit Nominal Ledger

=AP(“RangeDef,[(300..399)],s,a,-1,0,ab”)

=AP(“RangeDef,[(2200..3195)],s,a,-1,0,ab”)

Inserts a listing of all accounts in the range 300 to 299 where there is an active balance in either the current or the prior year for the account.

Inserts a listing of all accounts in the range 2200 to 3195 where there is an active balance in either the current or the prior year for the account.

For the Nominal Balances

To insert the balances for the current year next to each account, use:

3 Digit Nominal Ledger

4 Digit Nominal Ledger

=AP(“RangeCode,[(300..399)],s,a,-1,0,ab”)

=AP(“RangeCode,[(2200..3195)],s,a,-1,0,ab”)

Example

To list all accounts that make up turnover, based on their account numbers you would use the following:

B

C

D

10

=AP("RangeDef,[(001..099)],s,a,-1,0,ab")

=AP("RangeCode,[(001..099)],s,a,-1,0,ab")

11

   

12

=SUM(C4:C5)

=SUM(D4:D5)

13

   

As lines are inserted the sum range will be increased. Alternatively you could have created the total by referring to the total of the global range using =AP(“a,001..099) and =AP(“b,001..099]).

Page statistics
1491 view(s) and 5 edit(s)
Social share
Share this page?

Tags

CCH Accounts Production

Comments

You must to post a comment.

Attachments

 

 | Cookie Policy | Copyright | Privacy Policy Terms of Use | Contact Us |