Introduction

This article will teach you how and when to build formulas, and when not to.  I will be updating this article with more topics and more pictures, so check back!

Let’s start with the basics, some terms you will need to know.

Item – A single line item in an Estimate.  Has a Takeoff Quantity and a cost (Example: 2000 PSI Concrete, CY, Material $1000)

Formula – A named calculation used to determine the Takeoff Quantity of an item. (Example: CY-Concrete)

Variable – An individual question within a Formula.  Two types of input are accepted, numeric and yes/no.

Assembly Variable – A Variable that exists solely for the use within an assembly.

Custom Calculation – An in-line Formula created within an assembly.  It cannot be reused.

Assembly Calculation – A Custom Calculation field that allows you to generate a “quantity” for an Assembly.  This allows Timberline to display a cost per unit for an entire Assembly.  Standard Formula rules apply.

“Z” Formula – A special formula that is first added at the beginning of the Assembly Calculation.  The purpose is to initialize your Variables in a particular order to help the estimator using the formula.  This formula is always times zero so it never affects the output on any other formula.

A formula is a powerful addition to your toolbox as an estimator.  Think of them as the way to transfer your estimating knowledge into a computer.  For example let’s say that you are always estimating concrete slabs.  You can create one or several Formulas that will ask you for a respective chunk of the slab in terms of Length, Width and Depth.  You simple break the surface of the slab into rectangular chunks and feed the Variables (L,W,D) into Timberline.  This will calculate the Cubic Yards of Concrete that will be consumed.  Normally you would have to do this with a calculator and then place the CY in the quantity on your Concrete line item.

Formulas can be used to help you with individual items or with items in assemblies, either way there is a one-to-one relationship between an Item and a Formula.  You can apply the same formula to many items but an Item can contain only one Formula.

Audience

Anyone who uses Timberline Estimating for line-item costing can benefit from formulas.  This article is geared towards the database developers and advanced users.  However, once you have applied formulas to the database, every user will take advantage of them.  Building Formulas are the essential foundational piece of harnessing the full power of Timberline.

Features

Ok Estimators, this is the moment you have all been waiting for, formulas!

Things that you can do with Formulas:

A quick scenario:  You are estimating a project and you have a concrete foundation to takeoff.  Inital plans come in and you see a 44′ x 48′ rectangular slab.

Old Solution:

Take a calculator multiply Length  x Width x Depth /27 and you punch that number into your spreadsheet for your concrete item as Cubic Yards.

The Problem:

The initial plans you received were revised, the Depth and Width of the slab have changed.  The only way to fix it is to recalculate all of the data with a calculator.  This may not seem bad in the example but imaging adding up hundreds of small segments prior to getting one of these variables!

New Solution:

When you added the concrete item to your spreadsheet, a pop up window opens asking for 3 variables: Length, Width, and Depth.  All are answered discretely and stored separately.  When you click OK in the window, the Takeoff Quantity of the item is plugged in automatically.  When the revisions come in you can revise the inputs and it will automatically recalculate the takeoff quantity.

You can also create conditional Formulas, only calculating when appropriate, asking yes/no questions, etc.

Also as mentioned before, Assemblies rely heavily on Formulas.

When not to use a formula

First, do not overcomplicate things!  Always write formulas like you are a complete novice who has no idea how to estimate.  Ask simple questions, make pick lists that are simple, and give help in the variables if there is any chance it could be misinterpreted.  Once you get the hang of writing formulas, you may feel inclined to create a mound of large formulas, while they can be helpful to you, make sure your other Estimators know what is in there and what is not.  Documentation is key for this, take lots of notes.

I digress…The only time you should not use a formula is when an item is too simple or too complicated.  For example if you have a huge spreadsheet in Excel that calculates your man hours for your job, just plug in the output from that, don’t recreate the wheel unless it is creating problems in the office.

Planning a Formula

You will need to do a little planning and make sure that you have all the components to create the formula.

First, make sure you have your Item created in the Database that you want to attach the formula to.  You will need to know what the Takeoff Unit of Measure is for this item.

Next take out some scratch paper and write down the formula on paper.  Write it in plain English (or whatever your language) terms that are very specific.
Length of Wall * Height of Wall / (Stud Spacing / 12)

When writing this out, think about breaking this into smaller reusable formulas.  For example taking the Length of Wall * Height of Wall and making a formula called Wall Surface Area.  Then making a formula called Stud Spacing.

Then the resulting formula would look something like this.

Wall Surface Area / Stud Spacing

Now you will need to create variables that don’t exist in the database.  See section below on Variable Creation.

Building a Formula in the Editor

Here is where we begin building the actual formula.  If we are missing any variables we can create them as needed.

Click on Database  -> Formulas… from the menu.  This will show the editor.

Let’s focus on the four boxes at the top of the editor.  There are also four corresponding colors used in the actual text of the Formula.  The entire bottom half is the actual editor where you type or input components into the formula.  The four components are:

• Variables – The endpoints of the formula.  The inputs from the user.  Variables have Blue Text with Underline
• Operators – The arithmetic, logical, trig and special functions. Operators and Numbers in black.
• Formulas – You can insert (nest) another formula’s result into this one.  The Variables will be combined when the formula is used.  Formulas are Green With Underline
• Formula Tables – A Formula Table stores many Formulas and picks the correct one based on inputs (Variable). Creation of these is beyond the scope of this Post.  Formula Tables are Violet with Underline

OK so the basic rules of writing an formula:

• To include an element from one of the four boxes, simply scroll to it within the box and double click it.  Make sure your cursor is in the proper position in the editor before you do it.
• Do not attempt to type anything but Numbers and Operators, in other words only Black from above.
• If a Variable, Formula or Formula Table are not colored properly erase it and select it again.  Sometimes an accidental backspace can do this or trying to type it manually (see rule #2).
• This is the part Excel and spreadsheet users will be at home with.  The syntax for the formulas are almost identical for most operations.  Just imagine instead of making references to cells on a spreadsheet you are using a Variable to capture that number.

Variable Creation

This is a necessary section, not terribly difficult but there are some topics that need to be covered outside of the standard documentation.

You can create variables on the fly or ahead of time when in the planning phase of your formula.  To edit a Variable that is already in your formula, right click it and choose Edit Variable… To create a new variable without going through the menus, right click in the Formula Editor Window and choose Create Variable…