There’s no right or wrong way to do anything in Revit, though some methods are more scalable. When writing formulas for families, it can prove advantageous to make it as human-readable as possible making edits faster and easier.
Through a process I call “funneling”, you can take multiple checkboxes and assign them an easily referenced value from an integer parameter. Imagine a heat pump available in three voltages (208/230/460V) and two frequencies (50/60 Hz) available with and without a heater or a humidifier. Each combination produces a different Rated Load Amps, 24 possible values in total. By this point, most will have resorted to using a type catalog, but this can easily be handled using instance checkboxes.
First, we create checkboxes for each available voltage and an integer parameter. The integer parameter should not be named “Voltage” since we’ll probably want a true Voltage parameter later, so I use “Voltage Selection”. Write a formula that looks at each checkbox and assigns a numerical value based on which one is selected.
I prefer to go in ascending incremental order for legibility and I never use the “False” of the statement for a usable value. This ensures that none of the options are missed in the funnel and counting parenthesis is easy since it’s equal to how many options we have. The funnel parameter is the separation between input and output, allowing us to change our outputs without affecting inputs and vice versa. By doing this with the remaining options for frequency, humidifier, and heater, we can use Excel to concatenate our 24 possible outcomes with a simple “if(and(“ statement referencing each parameter to a value like a combination lock.
Array out your combinations almost like you would for a type catalog and start a concatenate for just one row in another column. Lock the referenced cell holding the funnel parameter names in the columns and rows direction (with a “$” before each letter and number) so they don’t move at all. Lock the funnel parameters values in the column direction (with a “$” before the number only) so each formula fragment will reference the appropriate value for that combination when you drag the concatenate downwards, but not allow the cell with the reference parameter name to change at all. Then concatenate the formula fragments at the bottom and copy over into the cell next to it. Input a closing parenthesis in the cells the new concatenate references. This will count your parenthesis for you. Assign a false value for the formula and concatenate the three parts together:
- The concatenate of the true values
- The false value
- The concatenate of the closing parenthesis
Once created, the whole structure is scalable. The parameter names or values can be changed at any time and the formula will still work because the concatenate updates automatically. The structure can also be easily edited to create formulas for the rest of the family by adding or removing formula fragments (Rows) or referenced parameters (Columns) independent of each other. Multiple formulas can also be written at the same time if they reference the same parameters as seen at the end of the video. It would of course be a good idea to save the Excel Book wherever the family is saved if you ever want to come back and make changes later.
I hope this helps you create some powerful formulas! Feel free to leave me a comment below if you have any questions.