Rounding and Column Totals

If you have a Schedule Table column that displays real-number values that are rounded by the applied Property Data Format and you also display a total for the column, you may find that the total displayed does not equal the sum of the individual numbers displayed. This can happen because ACA uses the real-number values before the format is applied when calculating the total, and then applies the Property Data Format to the result. So if there is not a balance between round up amounts and round down amounts, the total can differ from the sum of the displayed, rounded numbers.

If this bothers you (it bothers me), you can make use of the same techniques discussed in this blog article, where the task was to get rows with apparently similar values to collapse into one row in a Schedule Table with a Quantity column. The rows were not collapsing because even though the displayed, rounded numbers were the same, ACA was using the actual values, before applying the Property Data Format, to determine whether or not the values were the same.

In response to an inquiry in the Autodesk ACA Discussion Group, I posted a sample file (AutoCAD 2010 format) demonstrating several ways a Formula property can be used to make the source value the same as the rounded value, so that the total for the column will be the sum of the displayed values (and, if you have a Quantity column, that rows with identical displayed values will collapse). The image below shows the test Schedule Table I set up to show how the various properties in the SpaceObjectAreas Property Set display. The file was created using the Aec Model (Metric Stb).dwt template and the AutoCAD Architecture (US Metric) profile.

The columns in that Schedule Table display two properties from the out-of-the-box SpaceObjects Property Set Definition: Name and Number. The remaining columns display properties from a custom Property Set Definition called SpaceObjectAreas. Here is a description of these properties, and the effect that the way they are set up and formatted has on the values displayed and the column total.

• BaseAreaUnformatted: This property, displayed in the column titled "UNFORMATTED", uses the Automatic Base Area property of Spaces to display the area of the space. A custom Property Data Format called Standard-8 has been applied to this property and to the column in the Schedule Table. This is a copy of the out-of-the-box Standard Property Data Format, with the real-number precision changed to eight decimal places and trailing zero supression turned off. There is also no prefix or suffix applied to the value, so when referenced by formula properties, the value will be treated as a numeric value. The areas of the test spaces do not generate any rounding issues when totalled, as they do not require more than the eight digits displayed.
• BaseArea: This property, displayed in the column titled "AREA FORMAT", also uses the Automatic Base Area property of Spaces. It has the out-of-the-box Area Property Data Format applied to it, which in its metric version displays real numbers using an Area unit type, units of square meters, decimal unit format with two-decimal-place precision and a suffix of " M2". Other than Space 101, the area of each space is rounded up when displaying the value to two decimal places. The total is rounded based on the total of the raw values (see the Unformatted column total), and results in a column total that is 0.04 M2 less than the total of the displayed numbers.
• BaseAreaPassThrough: This property, displayed in the column titled "PASS THROUGH", is a Formula property. It takes the BaseArea property and uses the feature added in ACA 2007 that allows you to assign a different Property Data Format for the purposes of the Formula property. In the example file, a custom Property Data Format called Standard-2 is assigned, which is a copy of the out-of-the-box Standard Property Data Format, with the real-number precision changed to two decimal places and trailing zero suppression turned off. The Formula takes the value of BaseArea (rounded to two decimal places by the Standard-2 Property Data Format), makes it a string, converts that to a double-precision real number and passes it through as the final value. Since we want to have a total value for the column, using the default Area Property Data Format would not work, since this includes a suffix of " M2", which would cause the value to be interpreted as a string and therefore not provide a numeric value that can be totaled. Because the Property Data Format has trailing zero suppression turned off, the CDbl conversion is not required, but because I know this value should always be a number, I put it in, should the Property Set ever get copied to a file that already has a Property Data Format called Standard-2 that has trailing zero supression turned on. In that case, if the BaseArea value was a whole number, the result would be interpreted as an integer in ACA and the real-number formatting would not be applied.
  
  Because the Formula property is generating the "raw" values with the area values rounded to two decimal places, the column total reflects the total of the actual values displayed, rather than the rounded sum of the source areas of the actual Spaces.
• BaseAreaRounded_01: This Formula propery is displayed in the column titled "FORMULA ROUNDING 0.01". It accomplishes the exact same effect as the pass-through Formula, but uses the VBScript Round function to do so. CDbl( Round( [BaseArea], 2 ) ) It also assigns a different Property Data Format to the BaseArea property (Standard-8, in this case). In the example file, I could have simply referenced the BaseAreaUnformatted property without reassigning the Property Data Format in the Formula property, but I used the BaseArea property with the reassigned Property Data Format to demonstrate that you do not need to set up a separate, unformatted version of an automatic property if you are using the 2007 or later version. I included the unformatted property here so that I could display the area values to eight decimal places in the Schedule Table, so you could see the effects that the various formatting/rounding options had on the raw numbers. As with the previous formula, the column total matches the sum of the displayed values, because the raw value of the Formula property is the same as the displayed value. If you can achieve the precision and rounding you want with a Property Data Format, then you can use either the pass-through or the Round function method. As you will see in the final example, the Round function (combined with other mathematical operations) can do things that the Property Data Format cannot do, in which case the Round function would be the only choice.
• BaseAreaRounded_05: As seen in the column titled "FORMULA ROUNDING 0.05", this Formula property rounds the area value to the nearest 0.05, and since the raw values are the actual rounded values, the column total is correct for the displayed values. CDbl( Round( ( [BaseArea] / 0.05 ), 0 ) * 0.05 ) The formula achieves the desired rounding by starting with the BaseArea property value, with the Standard-8 Property Data Format applied. It divides that value by 0.05, rounds the result to the nearest whole number and finally multiplies that result by 0.05 to achieve the desired result. I am not certain how often something like that might be used in a metric file (you could use the same technique to round to the nearest half-square meter, just change the "0.05" to "0.5"), but there are occasions, when using imperial units, that rounding room areas to the nearest 25 or 50 square feet is used as a means of not implying too much precision in early test fits.

In all of the Formula properties, the Area Property Data Format is applied to the property (and the column in the Schedule Table) to get the " M2" suffix applied to the value calcuated by the formula. Also remember that when specifying a property reference in a Formula property, you have to select the referenced property from the Insert Property Definitions box in the lower left corner of the Formula Property Definition dialog; you can not simply type in the name of the property, enclosed in square brackets. If you are interested in reading more about rounding in Formula properties or some of the other techniques discussed above, you may want to check out some of these earlier blog articles:
07/09/2005 - Unformatted Properties and Numeric Precision
08/31/2005 - Rounding Up Property Data Values
08/31/2005 - Rounding Revisited
09/03/2005 - Rounding Redux
09/15/2005 - Rounding to Death
05/01/2006 - Using Property Data Formats to Force Real Number Interpretation
04/15/2007 - ACA 2008/ADT 2007: Setting a Different Property Data Format in a Formula Property
06/16/2007 - Rounded Values and the Quantity Column