## 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]).

## Comments