Subtotal accuracy in schedule

[Anonymous]

Hi,

All numbers in the GFA column is set to display up to two decimal places, hence the total will be off if you take the total of the values listed, as shown in the last row. Using a calculator, the total should be 35282.94sqm.

I’ve looked up for online solutions and none works. I would let it pass but the officer who checks my submisssions will not allow us to use the auto generated total. How do you solve this?

[constantin.stroescu]

I think the problem came from the fact that you fixed to two decimal places. Every value is rounded a little but the real value is slightly different...and the sum calculated by Revit is more accurate ( the rounding is applied only to sum not to every item)

Cange to three decimal places and no roundings ...the values will slightly change and  I suppose that the difference will be no more...

[Anonymous]

Hi Constantin,

That would also be an option I'd explore except that the authorities who receive the revit file will not accept a 3 decimal places in the tabulation. So I was looking for another solution other than adding decimal places.

[constantin.stroescu]

I'm afraid in this situation that the very accurate way of working of Revit is not appropriate.....Using fewer decimals there is a small difference between the real Sum and the arithmetic Sum of the items...and in practice this is not wanted.....

In this situation I think that we can make an export for Excell where we can do a simple Sum ( it will not take in consideration the real values but the shown values):

1. do not Calculate Totals of Area and from Field Format set Unit Symbol to None
2. Application Menu (R) > Export > Reports > Schedule > export as delimited text -  .*txt
3. Open .*txt file in Excell and make there the sums

[Anonymous · Accepted solution]

Hi Constantin,

Thank you for your help and quick response.

The authorities now issued a revised template when I sent this same question in their help page. They added a hidden column where the area is mulitplied by a hundred. Another hidden column is used to round this number. The new GFA column divided this "rounded" number by 100 to derive accurate results. 🙂 The workaround looks like this:

actual value : x100 column : round column : new GFA column(x/100)

100.0042   : 10000.42      : 10000.00      : 100.00

150.0325   : 15003.25      : 15003.00      : 150.03

total                                                         : 250.03 (old method = 250.04)

Again, thank you.

[constantin.stroescu]

ok ! thank you for information...I spoke here with an accounter and he said me that the same method is applied at us in similar cases....

thanks again!

[BIM_Dreamer]

hi, i'm having same issue with you. dont mind to share the formula on how you get this round numbers of yours. please see attached. thanks

[David_W_Koch]

Here is one possibility.  Here is a room schedule I put together (pardon the imperial units):

The Area Parameter column shows the out-of-the-box Room Area parameter, rounded to two decimal places, with a total that is 0.01 higher than the result you would get if you added the displayed numbers, due to the fact that Revit adds the raw numbers first, the rounds the total to two decimal places, rather than adding the rounded values that are displayed.

The Area Unformatted column is a calculated value column.  The formula here is simply Area.  This column is formatted to show eight decimal places, so you can see the (approximate) number Revit is getting for the total, which is then rounded to two decimal places.

The remaining three columns are also calculated value columns, and are what you would need to force the values being added to be the values actually displayed.

• The formula for the Area100 column is simply Area * 100.
• The rounding occurs in the Area100Round column, which has a formula of (round(Area 100 / 1 SF)) * 1 SF.  The round function is expecting an unformatted real number, so if you give it an area value, it will bark about inconsistent units.  To get around that, the Area100 value is first divided by 1 area unit (1 SF here; you would want 1 m2) to remove the units, the rounding is done, and then that result is multiplied by 1 area unit to get the result to be seen as an area value again.
• The Area Fixed 2 Decimal column divides the Area100Round column by 100, resulting in a number that has exactly two decimal places, matching the rounded result Revit displays in the Area Parameter column.  The formula here is Area100Round / 100.

You would not need to include Area Unformatted column in your schedule.  You would most likely want to hide the Area Parameter, Area100 and Area100Round columns, leaving just the Area Fixed 2 Decimal column visible.  You also will likely want to use a different heading for it; the names used for all the calculated value columns are for explanatory purposes only.

[ToanDN]

Rounding the numbers at a higher precision then bringing them back to the original format. Very clever indeed!

[BIM_Dreamer]

Hello David, thank you for sharing this, very much appreciated. is there a way you can post the file so i can use as guide, i'm still struggling to learn revit. thank you.

how to make 8 decimal places?

[David_W_Koch]

I will post the file later.  For now, here is how to set the formatting for a parameter included as a column in a schedule.  Open the schedule view, and, on the Properties palette, under the Other category, select the Edit button to the right of the Formatting property.  Select the parameter whose formatting you wish to edit in the Fields list box in the upper right of the Formatting tab of the Schedule Properties dialog.  Then select the Field Format button.

In the Format dialog, uncheck the Use project settings toggle at the top, if checked.  Set the Rounding drop-down list to Custom, and type 0.00000001 in the Rounding increment edit box to get eight decimal place rounding.

[David_W_Koch]

Here is a project file, done in Revit 2014, that has the Room Schedule.  I added an additional calculated value column at the far right that demonstrates that you can do the entire calculation in one formula:  

((round((Area * 100) / 1 SF)) * 1 SF) / 100

It is easier to understand what is being done using separate columns, but in a project, you may prefer to do all the calculations in one column.  (You still need the out-of-the-box Area column, and would probably want to hide that column.)

[BIM_Dreamer]

Thank you for sharing appreciated. I will use this as guide.

[David_W_Koch]

In response to a private message (please post additional questions and requests in the forums, so all can benefit from the response) asking how to round to the nearest 5 (or nearest 0.5), I am posting the attached file, which is a revision to the one posted earlier (and still in Revit 2014 format).  Two additional columns have been added to the right side of the schedule, one for rounding to the nearest 5, the other to the nearest 0.5.

To round to the nearest 5, divide the number to be rounded by 5, do the rounding, and finally multiply the rounded amount by 5.  The Revit formula looks like this:

((round((Area / 5) / 1 SF)) * 1 SF) * 5

To round to the nearest 0.5, you would have to multiply the number by 10 and then divide by 5, for a net result of dividing by 2.  After rounding, multiply the result by 2:

((round((Area * 2) / 1 SF)) * 1 SF) / 2

[chrisplyler]

David I could certainly figure that out, but it would take me several minutes. It wouldn't come to me immediately anymore like it would have thirty years ago back in high school. If you're able to just whip the required formula up off the top of your head in a second, then I hate you forever and ever and ever.

[David_W_Koch]

---

@chrisplyler wrote:

David I could certainly figure that out, but it would take me several minutes. It wouldn't come to me immediately anymore like it would have thirty years ago back in high school. If you're able to just whip the required formula up off the top of your head in a second, then I hate you forever and ever and ever.

---

It took me a few minutes as well, and I updated the file as much to make sure the math worked as to serve as a live example.  Proof I am not all that clever:  the second formula in my post is correct, but the words have the multiply and divide backwards.  It should read:

To round to the nearest 0.5, you would have to multiply the number by 10 and then divide by 5, for a net result of multiplying by 2.  After rounding, divide the result by 2:

((round((Area * 2) / 1 SF)) * 1 SF) / 2

If it makes you feel any better, I had spent a lot of time dealing with rounding in AutoCAD Architecture formula properties.  They use VBScript, so are somewhat more robust than the formulas allowed in Revit (you can specify a number of decimal places), but the math for rounding to something other than 1 is the same.

You can still hate me if you want.  😉

[chrisplyler]

---

David_W._Koch wrote:

You can still hate me if you want.  😉

---

It's a tough tough decision...

On the one hand I want - and am glad for - you to be a genius. On the other hand I like to be the smartest one in the room LOL.

[jagostinhoCT]

Hello,

Very clever indeed.

I am looking for rounding down of area values but in Room Tags. I guess these cannot be hacked to always round down, say, 23.4 to 23 and samewise 23.7 to 23.

Thanks

J

[David_W_Koch]

@jagostinhoCT

Sorry for the delayed response.  I am quite a bit behind in the Revit Architecture Forum.

If you want to have just the value always round to the next lower number, use the rounddown function, instead of the round function.  Note that for negative numbers, this will result in an increase in the absolute value of the ones digit.  So, rounddown(-3.1) = -4.  That should not be a problem with Area values, as those should not be negative.

[jagostinhoCT]

Hi @David_W_Koch,

Thank you for your reply.

My issue was not with schedules but with the rounding down in Tags.

Found that my request is now possible in Revit 2017 as you can use formulas in Tags.

Thank you once again.

J