RevitForum Blog: Working with Schedules – Relationship between areas.

In this recent thread on www.revitforum.org a question was brought op on how to calculate the Ratio between two Area Types. I’ve dealt with this a lot in trying to create a Template for Building Code Calculations, and came up with a solution.

A copy of the sample file used in this post is found here.

The problem with Revit Schedules is that it’s not possible to make calculations based on a Grand Total. Revit will only calculate per row, and this might not be what you’re after.

An important rule in the Dutch Building Codes, the 55% rule: 55% of the usable area of a building (“gebruiksoppervlak”) must be qualified as suitable for the long-term presence of people (“verblijfsgebied”). This kind of Area is required to meet all kinds of rules that we just ignore. This blog is about the question: how can I calculate the ratio between “gebruiksoppervlak” and “verblijfsgebied”?

To begin, I just modelled a very simple geometry and placed some Rooms. Now it is important to identify the Rooms that actually belong to the type “verblijfsgebied”. As mentioned, the the rules for assigning a Room to the type “verblijfsgebied” are left for another time, so I pick a random room that I want to assign as a “verblijfsgebied”, while the rest is going to be assigned to “gebruiksoppervlak”. The assignment of a room or area to a certain Building Code Type is still fairly easy:

1. Create a Shared Parameter “VO_naam” for the type of Room and / or Area. This is a text parameter. Group the Parameter under Identity Data, make it an Instance Parameter and assign to the appropriate Category, in this case Room (which can also be Areas).

2. Give the various Rooms correct values of the parameter, in this case and GO and VG1.

3. Make a Schedule of room. Add the following fields:

a. Name

b. Area

c. VO_naam

Now it gets more complicated: how can a Revit decide whether a room belongs to “verblijfsgebied” (VO) or “gebruiksoppervlak” (GO), and how to calculate the proper Area?

First off, let’s start with defining the type of Room. To define this a Yes/No Parameter can be used: the user manually checks off whether a Room belongs to the VO or the GO. There is also a more automated (but also complicated) way: by using a so-called Key Schedule. The Dutch Building Code has but a limited number of types of rooms. That certain types of rooms have properties and rules assigned to the, and one of them is whether the type of Room belongs to the VO or GO. Since the Key Schedule can also be used for other purposes and calculations, this method is applied here.

- Create a Key to Schedule: View Tab> Schedule. Select as a type of Schedule the Room Schedule and select Key Schedule.
- Assign a Key Name and an additional parameter: OF_VO (Conversion Factor_VerblijfsOppervlak). This is an Integer.
- Press the right mouse button in the Schedule View and choose Add Row. Add as many rows as desired.
- After adding the appropriate rows can be filled the different types. Additional Rows may be added at any time during this excersize. Fill OF_VO parameter. In this case, only the Room Type “verblijfsruimte” is part of the VO (value 1) and the rest of the room types are part of the “gebruiksoppervlak” (value 0).

Setup of the Key Schedule

Once this is completed you can return to the Room Schedule and add the Parameters from the Key Schedule to the Fields in the Room Schedule.

Next, select the parameter VO_oppervlak again and press Edit. In the Formula bar add the following formula: Area * OF_VO. Because you have indicated in the Schedule Key that only the Rooms with the Room type “verblijfsruimte” have a conversion factor of 1, the result of this formula will be 0, unless the Room Type ‘verblijfsruimte” is applied.

Second: how can we calculate the Ratio between two types of Area? This cannot be done without some trickery. The problem is, that I can format the Schedule in a way that it will only show two rows, one with VO_naam set to GO and one with VO_naam set to VG 1. I can also create a Ratio-parameter which calculates the Ratio between the two. Just add the Parameters Ratio = Area VG / Area GO.

But, there are a few rules to using Schedules for calculations which causes problems:

- Revit will calculate a formula for each Room. This DOESN’T change when Itemize Every Instance is unchecked.
- In other words: unchecking Itemize Every Instance does NOT change the outcome of any given Calculated Value in the Schedule. It will look this way because Revit can add up the outcome of all Rows that are hidden, but the outcome of each Room is still the same. In this case we want to reference to the Grand Total Area so this causes a erroneous outcome of the formula:
- The formula will read Area VG / 0 = (cannot be solved), when the Room is assigned to Room Type “VG”.
- The formula will read 0 / Area GO = 0, when the Room is not assigned to Room Type “VG”.

This outcome is shown in the next few figures and was also the problem in the message posted on Revitforum.

* *

* *

* *

* *

* *

* *

* *

Even though the Grand Totals are correct, the calculated Ratio stays 0%

But how can we now calculate the ratio between “verblijfsruimte” en “gebruiksoppervlak”? For starters, go to the Formatting Tab an check the box Calculate Totals for Area and VO_oppervlak. Then go to the Sorting And Grouping Tab, set Sort By VO_naam on and check Grand Totals.

Switch to “Sorting and Grouping”, Sort By “VO_naam” and check Grand Totals.

After this we create an additional Calculated Value. Create a Calculated Value of the type Percentage and divide Area by .This shows the percentage of a single Room to the overall Grand Total Area.

After this we create another Calculated Value, “Area_totaal” with the formula: “Area / Verdeling VO_GO”. With this parameter the Grand Total Area of all Rooms is calculated . Crucial in this step is that the Grand Total Area now is a Room Parameter and therefor appears on each Schedule Row. This makes it possible to use this value in further calculations.

The third Calculated Value is “Percentage_VO” and uses the formula “Verdeling VO_GO / Area_totaal”. This parameter calculates the total percentage of Room type “verblijfsruimte”.

Finally a Calculated Value “Percentage_GO” is created with the formula “(Area-Distribution VO_GO) / Area_totaal”.Theoretically, also the formula “100 – Percentage_VO” could be used, but this formula gives a good check if everything is calculated and whether all available Rooms have been assigned to a certain Room Type.

It should now only be a matter of setting the proper Schedule Formatting. The columns

“Name”, “Area”, “Type ruimte”, “OF_VO”, “Verdeling VO_GO” en “Area_totaal“ can be hidden in the Schedule. Click the right mouse button on the column and select Hide Column. Then go to the Sorting And Grouping tab and uncheck “Itemize Every Instance”. Next, in the Format tab, the formatting of the columns “Percentage_VO” and “Percentage_GO” can be adjusted. Set the Units to Percent, 2 decimal places and with the unit symbol %. The end result should look something like shown in the figure below.