Calculated fields in schedule

[MikeJarosz]

I have created a department area summary schedule. Program areas are a shared parameter, entered manually. Formulas calculate departments that are over and under the approved program. For the over area, the formula is IF ( Area > Program Area, Area - Program Area, 0 )

In some instances, but not all, the comparison fails. Note on the attached screen copy, Dept D Staff Support computes correctly while the line below it, E - Infirmary fails.

Any ideas?

88996

[CADastrophe]

My initial speculation is that there may be identically named Rooms that exist in the Project, judging by how your "Under" Calculated Value returns a null value. This situation is easily created because deleting a Room or Space element in Revit does NOT remove it from the Project. Create another Room Schedule that lists ALL rooms, sorted by Name and with the option "Itemize All Instances". This should indicate if this is indeed the case.

[DaveP]

I was going to refer you to this (rather lengthy) thread
http://forums.augi.com/showthread.php?125408-Area-of-quot-Not-Placed-quot-Rooms&highlight=enclosed
but I see you've already replied to it!

[MikeJarosz]

I found the problem. The program for utilities is 24,000SF. That is divided over three floors. When I made the Program Area parameter = 24,000, it used that number for every instance of the department. What I need is a way to apply the Program Area to the total area of all the department instances.

I could do this in Excel, but I would then have to be constantly updating external reports. I do have a schedule with all the rooms itemized. It's the summary that is the problem: how to compare the program area to the SUM of the department areas

[CADastrophe]

I don't believe that this can be accomplished without some level of manual upkeep, since one cannot reference the "Count" field with a Calculated Value. To minimize the manual upkeep and keep the schedule within Revit, perhaps the following could work:

1. Create a new Project Parameter (Integer / Instance) to the Rooms, for the purposes of providing a manual "Count" field.

2. Add the above Project Parameter and 'Count' as fields to your Schedule.

X-X-X-X- EDITED -X-X-X-X-
3. Revise the formulas of your Calculated Value to:
IF ( Area > Program Area , Area - ( Program Area / [ProjectParameter] ) , 0 )

The IF function will yield incorrect results, due to the "If False then 0 sqft" component. This is because it calculates every instance separately in this formula and then tallies them, rather than the sum shown on the schedule. This means that if a room following this formula has an area less than average Program Area, it contributes 0 instead of adding the negative value to the total as it should, thus invalidating the data.

Anyway, your best bet is to have a single Calculated Value that shows a positive for ABOVE and a negative value for BELOW, and then use conditional formatting to color-code the negative values. Use the formula:
Area - ( Program Area / [ProjectParameter] )
-X-X-X-X- EDITED -X-X-X-X-


The manual upkeep to this method lies within the Project Parameter for the manual count. However, this upkeep shouldn't be too bad, as you can maintain it within that schedule. If you compare the 'Count' field side-by-side with the Project Parameter, you can enter the proper total into the Project Parameters value to correct the count and project that value to the subsequent Rooms, thus correcting the Calculated Value.

[Alfredo Medina]

In regards to the original post, two comments:

When you use "if A is less than B" or "if A is greater than B" in a formula, be aware that there might be a third condition, not considered, when A and B are equal.
Perhaps instead of a column for Over and a column for Under, this could be simplified as shown in this image:

[jsteinhauer]

Sorry Alfredo, shouldn't the Formula be (Area - Programmed Area)? Just an observation/suggestion.

[Steve_Stafford]

Yes if the desired result is a "negative" when your room area is "short" of the required area.

[CADastrophe]

I think it's more complicated than that, though. From what I understand from his posts is that he is not making the calcs on a room by room basis. He's doing it by "department" and he stated that he has multiple instances of Rooms that are part of that "department". My response is also based on the fact that he wants to apply a value to "Programmed Area" in his schedule, which would serve as a user-set total value. Unfortunately, if he has 5 rooms in that department, each of them read that total value, which Revit sums up to 5 times what he wants as his total.

Example:

Desired value for "Programmed Area" is 600sqft, and there are three Rooms in this department, each at 200sqft.
(The 600 sqft value is NOT divided among the Rooms, hence my procedure in my previous response.)

Revit does this calculation in the schedule, since it is calculating totals (Area - Programmed Area for each Room, then totaled):
Room 1 + Room 2 + Room 3 calcs
(200sqft - 600sqft) + (200sqft - 600sqft) + (200sqft - 600sqft), which results in -1200sqft!

Am I making sense with this or am I out in left field??

[Steve_Stafford]

I don't think we can summarize area by department result by relying only on room data AND validate the total area against a fixed value criteria for department with a room based schedule. An area plan could though because the area a specified department needs can be supplied as a criteria just as he has done for rooms. The department alignment can be documented with a color fill plan according to departments and an area plan can be created to document the area for each department.

[MikeJarosz]

if he has 5 rooms in that department, each of them read that total value, which Revit sums up to 5 times what he wants as his total.

That is exactly what happened. This is a summary report. Notice those departments with no level listed have multiple instances. The departments that show floors occur only once. Intuitively, the program area parameter calculation ought to use the summary area shown in the schedule.

As for the <= and >=, As an old time Fortran programmer, I am aware of that trap. However, Revit documentation says they did not create <= and >=. They offer NOT(a>b) instead. Autodesk always has to be different. I was only testing my formula to see if it would work, which it did not. If it had, I would have gone back and adjusted the formulas.

Ultimately, I dumped the schedule into Excel and finished it there. But my firm does a lot of planning and area calcs, so I am interested in finding a solution to this problem

[Alfredo Medina]

Sorry Alfredo, shouldn't the Formula be (Area - Programmed Area)? Just an observation/suggestion.

Yes, I agree. Thanks. It would be better as Area - Programmed Area, highlighting the rooms that are not reaching the goal as negative values, as Steve said.

[jsteinhauer]

But isn't it as easy as assigning the correct value for each room, and then not itemizing every instance when you sort by department? Calculate the total for Area, Programmed Area & Area Delta. It should give you the correct values.

Cheers,
Jeff S.

[CADastrophe]

Attached is a 2012 Revit Project as a quick example of the method I was describing, in case it helps.

[CADastrophe]

But isn't it as easy as assigning the correct value for each room, and then not itemizing every instance when you sort by department? Calculate the total for Area, Programmed Area & Area Delta. It should give you the correct values.

The problem lies with the intentions of Programmed Area. The idea is that the user inputs this value into the schedule as a Total Area, but by inputting that value, it is projected unto each of the Rooms under that Schedule Line. If Calculate Totals is on, then Revit takes that inputted Total Area from EACH of those Rooms and sums them, resulting in a multiplication of that value that is already supposed to be a total.

[CADastrophe]

So we're pretty much doing the same thing in different ways: distributing the Programmed Area value amongst the Rooms. I suppose it's just a matter of how one would prefer to do the manual upkeep. Perhaps it would be best to select a single Room from each department and enter the Programmed Area there, instead of dividing it amongst the Rooms.

But regardless, creating the conditional formula to visually differentiate the positive and negative values is not possible if the unless the schedule is Room by Room, since the overall total values cannot be referenced by a formula. Any IF/THEN statement will not function properly if multiple differing values are returned.

[jsteinhauer]

Right. Which is way I would put the correct individual programmed area in to each room. Then in the schedule, I would sort by department, but not itemize ever instance. This gives you the total Area, total Programmed Area & total Area Delta, by department.

The attached Image shows the columns for Area, Programmed Area & Area Delta. The footer shows the total of each Department. When I uncheck Itemize every instance, I still get the correct readout.