Conditional formatting

So Steve stole the thunder this week and you already know how to do conditional formatting in Revit schedules. That Steve is one quick guy but after all, his readership eclipses my little corner here! Jokes apart, we were talking about how I stumbled across this hidden tool when trying to activate the Manage tab with shortcuts in Revit 2010. Now I just hope Autodesk doesn’t close the loop-hole. And guys, if you’re listening, please just add a button as we can use this tool in Architecture! EDIT: As of Revit 2010 SP3, this functionality is officially available. Thanks for listening!

The main shortfall I noticed is the fact that you cannot do conditional formats based on other parameters. That would be high on my list of enhancements for this tool. To get around this bump, one has to leverage the power of calculated parameters in schedules to perform the number crunching. A simple Yes/No parameter could be used to raise a flag and your condition would just look for the value and format the cell accordingly.

We can actually add multiple conditions to test for, but it is very limiting because it assumes an “AND” operator between them. What if I want an “OR” (which is what one needs when testing to see if a parameter falls between certain values and criteria….they can’t all apply at the same time! An “AND” operator expects all conditions to be satisfied and would thus force the overall condition to fail). It also took me a while to figure out how to add or remove conditions from the list. By the way, why does this dialog use the label “Field” when referring to a parameter? Seems to lack consistency with Revit’s terminology.

So I’ll stop rambling now and discuss how I plan on using this new find. The first two things that spring to mind are to check for human errors (egress calculations) and for room area comparisons between actual and target area.

Checking Occupancy

Unfortunately Revit cannot do calculated parameters in tags (my jaws are hurting repeating this one!) and so we have to resort to a “stone age” method: use a calculated parameter in the schedule and then manually type a copy of the result into a shared parameter that is added to both the schedule and the room tag so you can display the information in plan. PS: You cannot use the built-in Occupancy parameter because it is Text….DUH!

Yes, yes I know, the API can be used to copy values for you probably, but WHY do I need to resort to this for something that is a required task on every job by every firm? I’m getting side-tracked again….

So at least now we can use conditional formatting to help us identify values that don’t match the calculated values, which would happen when room sizes change. Here’s an example calculated parameter:

In the schedule Formatting tab, select the parameter you want to add the condition to and type Alt+N to reveal the hidden gem:

Here’s an example of what the schedule would look like when values don’t match up.

Note: You can check the option Hidden field in the Formatting tab to hide the calculated parameter used for the condition and it will still work just fine.

Some observations: Null values are ignored in calculated parameters (see the yellow cells above) and in conditional formatting. My condition “Flag = No” is not being met because the value is null, yet Revit is not coloring my cells. This needs fixed!

Room Area

For this scenario, I want to ensure that Actual Area:

1. Is never below the Target Area
2. Is never more than 5% of the Target Area
3. Never varies by more than 100 SF from the Target Area

So the formula for parameter Flag would be something like this:

or(not(abs(Area - Program Area) < 100 SF), % Area Variance < 0, not(% Area Variance < 0.05))

where Area is the Actual Area, Program Area is the Target Area, & % Area Variance is (Area - Program Area) / Program Area.

Here’s what the resulting schedule would look like:

In the above example I added a conditional format (in orange) to highlight cases when the % Area Variance was below 5%, but the area difference was 100 SF or more.

Some observations: It would be really valuable if we could specify the boolean between multiple conditions instead of having Revit assume an “AND” operator. And we should be able to specify different colors for the various conditions instead of just one!

If that were the case, one would be able to highlight rooms with areas above the target with one color, and others below the target with another color. Finally, I also noticed that for Yes/No parameters, the Conditional Formatting dialog would let me change the value of my parameter Flag to “Yes”, but it would revert it back to “No” when I clicked out of the dialog or clicked on something else. Not sure why.

All in all this tool is of great value and I’m sure lots of you will find various ways to use it. Let me know how you intend to implement it!

Conditional formatting