Round-Pound Arithmetic is a practical problem which was introduced by Iain MacCallum, University of Essex [CSM-212].
What is the problem?
In a spreadsheet, the displayed values may be different from the internal values due to rounding. When these differences are accumulated, the displayed values may look erroneous, even though the calculations are correct according to the internal values. For reporting purpose, these apparent errors are best avoided, as they could dent the creditability of the reports.
Example
Suppose you have a spreadsheet with the following values:
|
Internal
values |
|
|
Sum of rows: |
|
|
68.59 |
29.98 |
98.57 |
|
|
28.83 |
59.87 |
88.70 |
|
Sum of columns: |
97.42 |
89.85 |
187.27 |
Suppose you format the cells to display two decimal points only. Most spreadsheets will display the rounded numbers (the internal values remain intact). The following values will probably be displayed.
|
Displayed
values |
|
|
Sum of rows: |
|
|
69 |
30 |
99 |
|
|
29 |
60 |
89 |
|
Sum of columns: |
97 |
90 |
187 |
As the internal values are not visible, some of the sums appear to be erroneous to the viewers. These apparent error are indicated in red (in the first column, 69+29 is 98, not 97, in the final column, 99+89 is 188, not 187).
Flexible Rounding
One way to make the sums look right is to round the figures up or down flexibly. This means we would allow 97.42, say, to be rounded to either 97 or 98. However, 62.00, say, must remain 62 (not 61 or 63).
For example, the first table could be rounded to:
|
Displayed
values |
|
|
Sum of rows: |
|
|
69 |
30 |
99 |
|
|
29 |
60 |
89 |
|
Sum of columns: |
98 |
90 |
188 |
Complications
In the above examples, the raw data had two rows and two columns of independent values – they are values that the sums are dependent on. When there are more rows and more columns, there will be more chance of displaying erroneous sums.
Besides, the tables shown are two dimensional tables. Rounding would be more difficult when one has three dimensional tables. This is because more constraints will be present.
In general, the dependency of the values could form any acyclic graph. It is not clear whether solutions exist for any given graph through flexible rounding.
Constraint satisfaction
MacCallum proposed several algorithms for solving The Round-Pound Problem.
This problem could naturally be formulated as a constraint satisfaction problem.
Doing so would allow us to tackle the problem with
well developed constraint satisfaction techniques.
Resources:
Maintained by: Edward Tsang; Last Updated: 2011.01.11