Prospective Students | Research | Department | Intranet | Search | Site Map | Home

# Former Staff

## I.R MacCALLUM

### Solving the Problem of Rounding in Spreadsheets

Rounded arithmetic is the traditional way of presenting this-and-previous-year accounts. In such presentations there is always at least one point of correspondence between the end of the previous year (rounded) and the start of the current year (unrounded). Accountants like to see consistency at these points, as well knowing that their arithmetic is correct.

Currently available spreadsheets offer 2 methods: rounding to the nearest unit before arithmetic is performed in which case the arithmetic will be correct but the total may be wrong; or rounding after the arithmetic has been performed in which case the arithmetic may be wrong but the total will be correct. For example, given the sum

1.3 + 1.4 = 2.7

1 + 1 = 2 total wrong but arithmetic OK

1 + 1 = 3 total OK but arithmetic wrong

The traditional practice in accounting is to allow the components of a sum to be rounded either up or down (the error is now up to twice that of rounding to the nearest unit) but to constrain the total to be rounded to the nearest unit. Thus, in the example above accountants would allow either of
1 + 2 = 3 total OK and arithmetic OK

2 + 1 = 3 total OK and arithmetic OK

but would prefer the first. Implicit in this is the need for whole units to remain invariant. (This is not shown in the example above.)

The algorithm for vectors of any length is linear in time and can be applied at any unit: e.g. \$1, \$1,000, \$1,000,000, etc. It was known in the data processing community of the 1960s as Round Pound Arithmetic (RPA), but there appears to be nothing on this method in the literature and no attempt to implement it in spreadsheets. The algorithm is easily proved correct for vectors of any length.

Accounts are rarely as simple as mere vectors. A component of one vector may itself be the sum of another vector. This is the structure known as a tree. The algorithm has been adapted for arbitrary trees and formally proved it correct. The value at the root is the value which is rounded to the nearest unit. The algorithm is linear with the number of nodes in the tree.
Unfortunately, accounts are not even that simple. For example, in financial analysis one invariably finds that a component of one vector is used in more than one tree. This means that accounts are of the form of intersecting trees, that is, acyclic directed graphs.

A method based on constraint satisfaction with tabu list has been used to round arbitrary acyclic directed graphs according to the RPA constraints, with all "roots" rounded to the nearest unit. Using a test generator, it has been tested extensively on cross-tabulations in 2 and 3 dimensions (where every node is constrained to sum into to 2 or 3 separate totals), on arbitrary graphs, and on graphs representing sets of intersecting trees, these being the structure of financial statements.

Two very small graphs have been found (one of 4 nodes and one of 5 nodes) to which it is impossible to apply RPA. These are likely to be too small to be of interest to anyone using a computer. Fortunately, the more nodes there are in the graph, the more solutions are possible.

A few cases of 2 by 2 by 2 cross-tabulations have been observed in which the present algorithm fails although a solution within RPA constraints exists. Here, the search for a solution cycles because the tabu list is short (only 8 nodes) and the interconnectivity high. However, because the number of nodes is very small (28) they may be easily and very quickly solved by enumeration. The same problem has been noticed in some intersecting trees with a very high density of integers and consequently little room for maneuvering. None of these cases is likely to crop up in even modest use of spreadsheets. But even in these few cases where the algorithm does fail, it confesses and the number of anomalies which remain is no greater, and often less, than there would be with traditional methods. Further research into these cases may yield improvements in this area.

A demonstration program consisting of a simple type of spreadsheet has been written. Values may be rounded according to RPA constraints as well as by the two traditional methods. The method has also been built into a simple accounting program which is in regular use by a local organisation.
These demonstration programs and the investigative test generator are written in C++ for Sun or NeXT computers under UNIX. They have been transferred to Microsoft Visual C++ on a PC executing in a Command Window. A more convenient user interface will be provided shortly.

A general description of the method as applied to cross-tabulations was published in Software Practice and Experience, Vol. 26, No. 3, March 1996.

Iain MacCallum
University of Essex
9 February 1998

 Prospective Students | Research | Department | Intranet | Search | Site Map | Home