Revit: Schedule Column Totals and Rounding

Schedules in Autodesk^® Revit^® that include a total for a column of numeric data is subject to the same issues with rounding that Schedule Tables in AutoCAD^® Architecture have, as noted in this previous blog article, and expanded upon in this one. Both programs display formatted data in the schedule, which may involve rounding, but use the unformatted data when calculating the total for the column, and then formatting that total. If the data is rounded, then it is possible that the total displayed will not be the sum of the displayed values in the column.

In Revit, you can use one or more calculated value columns in the schedule to have the rounded value be the value that is used to calculate the column total, so the total will match the sum of the displayed values. The round() formula function will round to the nearest whole number. If you are displaying data to the nearest whole number, a single calculated value column, employing the round function will suffice. In the example shown in the screenshots below, the data is to be displayed with two decimal places. That requires a few intermediate calculated value columns. (Click on the image below to see it full-size.)

The Area Parameter column shows the out-of-the-box Area parameter for Rooms, rounded to two decimal places. The total of this column is 0.01 higher than the result you would get if you added the displayed numbers.

The Area Unformatted column is a calculated value column, that displays the Area parameter with eight decimal places. I included this just so you can see how the raw numbers total and then are rounded to get the total of the Area Parameter column. This column is not needed to generate the column with "correct" total.

In order to round the value to two decimal places, the first step is to multiply the Area parameter values by 100. The Area100 calculated column does that. The image below shows the formula.

The Area100Round calculated value column rounds the Area100 value to the nearest whole number. The round function requires an unformatted real number, so it will not work on an Area value. To get around the "inconsistent units" problem, you have to divide the Area value by one square unit, do the rounding, and then multiply by one square unit, as shown in the image below.

The Area Fixed 2 Decimal is the calculated value column that you will want to have displayed in your schedule (but probably not with that column header). This column takes the value in the Area100Round column, divides it by 100 and displays the result with two decimal places. Since the values in the Area100Round column are all rounded to whole numbers, dividing by 100 leaves, at most, two decimal places, so Revit is adding the displayed numbers when calculating the total.

You do not have to apply eight-decimal place formatting to the Area100 and Area100Round columns; I only did that to more closely show the values that Revit is using internally. In the final schedule, you will want to hide the Area Parameter (the built-in Revit Area parameter), Area100 and Area100Round columns. You have to keep them, so the values are available to make the calculations. If you prefer, you can eliminate the Area100 and Area100Round columns by combining all of the math in one formula: ((round((Area * 100) / 1 SF)) * 1 SF) / 100. It is easier to explain what is being done using separate columns, which is why I took that approach here.