Audit tools for spreadsheets
Help File, Spreadsheet Data Lists, List Numeric Cells, List Formulae, List Shapes, List Errors, List Comparison, List Analysis (Find and Mark) Functions, Random Sampling, Auto-delete Rows, Analysis of Strings and Numbers, Numbers from Strings. Number Arrays from Strings, ACBA Worksheet Functions, Comparing Numbers in Strings, Approximately Equal, Technical Support, Combining with ACBA-EWP
These analytical, investigative and manipulative tools are suitable for auditors and anyone interested in building and validating Excel spreadsheets. They have been designed so that they automatically generate a record of the investigation or analysis undertaken.
All the functions (except the worksheet formulae/functions) are presented to the user through a new menu item on the Excel 2003 standard menu bar called 'ACBA Functions'. The most regularly used items may also be called through a short cut tool bar.
The tools are grouped into eight categories
List Analysis (Find and Mark) - examines a list or database for duplicates, gaps in numeric series, similar values (either numerically or as a pattern) or big/small values
- List Comparison - identify missing records or critical values that have changed between two lists
- Spreadsheet Data Lists - the existence (or sources of) numeric cells, formulae, errors or shapes in a worksheet or workbook
- Random Sampling - a random sample generator designed primarily for non electronic (paper based) population sources
- Auto Delete Rows - automatically cleanses a database based on the field value of the first column
- Numbers within Strings - examines lists of strings that contain numeric values and report the values
- Number Arrays within Strings - examines strings for the numbers or groups of numbers that they contain and generates a temporary database (or array) of that information. The user accesses the numeric information by virtue of its position in the array.
- ACBA defined worksheet functions - including extracting numbers from strings and evaluating whether two numbers are approximately equal
The package of ACBA Tools may be downloaded (free and open-sourced) direct from the ACBA Functions discussion forum.
The package is supported by a fully integrated Help System which is provided for your local computer. The Help System is also maintained up to date on-line.
Spreadsheet Data Lists
This functions lists the sources, associated formulae and results of all the cells containing a number on the active worksheet. It is designed to allow reviewers identify those results that have been calculated by, or imported from elsewhere within, the spreadsheet and those figures that have been posted by some other means.
It is often very difficult to identify the one formula which is causing integrity problems in a spreadsheet, especially if these involve links to an external file. List formulae identifies every formula within a worksheet or workbook and lists the formula together with its source address and the result on a separate worksheet. This enables a much quicker means of reviewing formulae for potential errors than inspecting cells individually.
Shapes cover the whole range of drawing objects, buttons and other control objects that can be placed on a spreadsheet. Each shape can have its own macro which can be linked either internally or to a separate workbook. As with formulae, links to separate workbooks can cause integrity issues especially if the linked file is moved.
'List Shapes' lists all the shapes on each worksheet within the active workbook together with their macros, if they have one. The 'placement' column shows whether one shape is hiding another underneath it.
'List Errors' lists all the cells in a workbook that contain an error value, the formula that generated the error and a description of the error type.
This function allows user to compare lists (or databases) for missing records, changed critical values or to print the matching records in both lists. A full description of findings is created on a separate worksheet. The primary requirement is that the lists have comparable key values. Where this is not the case, the package offers the facility for creating a key field by combining two or more existing fields.
List Analysis - the Find and Mark Functions
ACBA Tools provides six functions for investigating lists held in Excel. These cover finding
- duplicate entries
- gaps in a numeric series
- entries which match a pattern
- entries which are within a specified numeric range
- entries which are significantly big (or little) in a numeric series
- mark the current list order
They look for and mark records in a database or list. Where the programming requires that the list be reordered to undertake the comparisons, you are given the opportunity to retain a record of the original order.
"Entries which match a pattern" cause special problems of interpretation when employing negative criteria. The help system offers a discussion on the potential pitfalls of the interpretation of the results.
NB the 'significant entries function' is designed for use by auditors and accountants for whom debits (usually positive numbers) and credits (usually negative numbers) are equally important. It disregards the normal arithmetic rules associated with greater than and less than. This function looks at the size of the number irrespective of whether it is positive or negative. The function uses the comparison descriptions 'bigger than' and 'smaller than' to distinguish them from the standard arithmetic operators.
Designed to help auditors deliver true random samples from paper based audit trail listings, but is also suitable for randomly selecting from records held in Excel. See Sampling web page for more detail.
Used mainly for cleaning up databases imported from other sources - see Oddments - but can be very effective when used in conjunction with Find and Mark Duplicates for preparing unique records for random sampling.
Analyzing Strings that Contain Numbers
As a general rule, numbers and strings do not mix well. The presentation and methods in mathematics has a syntax which is entirely separate from the way we speak and write. But we use numbers and numeric methods all the time in our oral and written communication. The following methods illustrate various approach to extracting the essential numeric data from text strings.
Numbers within Strings
Like the 'Find and Mark' functions, these functions investigate lists in columns. In this case the lists are expected to be strings (a group of characters that is not recognisable as a number) that contain one or more numeric values. The object of each function is to extract and report the value you need. Each of these processes can also be used as a formula and are described in more detail at Numbers from Strings.
The advantage of using these form based processes over the Worksheet Functions described below, is that when sharing a file the new user does not need the ACBA Tools software to read the results. They are reported as standard numbers. Also, like the 'Find and Mark' functions, the process generates a column heading of the precise process and parameters used. This acts as an audit trail to your analysis.
Number Arrays within Strings
Very similar to Numbers within Strings only under this process all the distinct numbers or number groups within a string are analysed and posted to an array (a temporary database of the data found held in memory). The user can extract various characteristics of the number such as its value, its start position within string, its string length by reference to its location in the array.
A further advantage of this approach is that you can select which of several numeric values you want to extract, by requiring them to meet specific criteria that you can dictate in advance (including standard criteria like minimum and maximum values).
The Worksheet Functions
These are functions like SUM, SUMIF, COUNT and COUNTIF, that are available for use in formulae in the worksheet cells. You access the ACBA defined functions in the same way as you would SUM or SUMIF.
ACBA Tools provides a series of functions for reporting or extracting the numeric values contained within strings. These functions broadly divide into two categories
- the extraction of number values direct from strings
- the extraction of number values held in an automatically generated number arrays
Extracting Numbers from Strings
These functions return those numbers that are either adjacent to each other or are separated by a full point only. No other non numeric characters are accepted. Essentially, the extracted values must resolve to a simple number or decimal fraction.
Two of the functions extract decimal values
- the decimal value at the beginning of a string - BEGNUM
- the decimal value at the end of a string - ENDNUM
- All the remaining functions extract integer values
- the integer at the beginning of a string - BEGINT
- the integer at the end of a string - ENDINT
- the Nth numeric digit found in a string - NthINT
- the first integer number starting at or to the right of a specified position within a string - INTfrmPOS
- the first integer number to the right of a specified character string ("FindChars") within a string ("SourceString"). This function also allows the user to set the position in the "SourceString" from which to start looking for the "FindChars" string - INTfrmCHAR
The last three of these functions return errors where the parameters supplied cannot resolve to an answer.
Generating a Number Array from a String
Each of these functions depend on the generation of an array of the numbers or number groupings contained in a string.
The single most important function tells the user how many number groups there are in the array. This defines how many positions (NUM) there are in the array associated with a specified string. The position values (NUM) range from 1 to the total number of groups found. This is the role of the function 'NUM_NUMARRAY' whose only parameter is the string value you want to analyse for the numeric values it contains.
The extraction of all data associated with a numeric group depends on its position in the array. The following functions each have two paramenters MyString (the string being analysed) and Pos (the position in the array). They provide access to
- the value of the number (can be either a whole number or a decimal) - VAL_POS_NUMARRAY()
- the starting position in the string - STA_POS_NUMARRAY()
- the length of string value of the number - LEN_POS_NUMARRAY()
- the non-numeric character immediately preceding the start of the number string - PRECHAR_POS_NUMARRAY()
- the non-numeric character immediately following the end of the number string - POSTCHAR_POS_NUMARRAY()
All values are positive. The user may investigate the PRECHAR and POSTCHAR values to determine the sign of a value.
A second range of functions, dependent on the number array approach, allows the user to determine whether a string contains a number based on the logical comparison parameters of =, < and >. These can be applied to the criteria of value, start position or number string length.
Comparing the Numbers within Strings
Each of the functions above reports those digits that lie next to each other and therefore resolve to simple numbers. The next two functions review every numeric digit contained within a string (irrespective of whether they are separated by non-numeric characters) and compares their presence or absence in a second string. The result of the function is TRUE or FALSE.
- COMPanyDIGIT - compares the numeric characters in two strings irrespective of the order that they appear within the string
- COMPordDIGIT - compares the numeric characters in two strings, but a TRUE result also requires that they appear in the same order
SUMDIGITS and NUMDIGITS
These two functions deliver the sum and the number of the numeric values contained in a string, respectively. If there are no numeric values both functions return zero.
The approximately equal function is designed to allow limited inequalities to be permissible within the automated environment without creating a fatal crash. There are two versions - one allows for a statement to be true if the variance is within fixed limits; the second allows for a statement to be true if the variance is within a fixed percentage.
Technical Advice and User Support
The ACBA Functions user group provides a forum through which both technical advice and exchanges of user views and feedback can take place. Membership of the user group is free of charge. This forum is restricted to ACBA Function users and postings to it are moderated by ACBA staff. It provides an excellent resource for both technical and operational advice and information. We recommend strongly that users join the group.
Tools for Pragmatists
While ACBA Tools has an intrinsic self-contained value, its use in combination with ACBA Electronic Working Papers is explored at Tools for Pragmatists.
Last updated 10 October 2016