Using formulae in spreadsheet columns

As well as displaying a wide range of fields in the spreadsheet, you can construct formulae - user-calculated expressions - that define precisely what information is displayed in a spreadsheet column. You can construct formulae to display practically any information in the spreadsheet: formulae can reference spreadsheet fields, user-defined fields and tables, variable dates, etc, and can take information from the live data or from a specified baseline.

On top of this, you can construct formulae to perform calculations on project data. For example, you could construct a formula to subtract the baseline cost from the actual cost in the live data, then display the result in a format of your choosing; or you could construct a formula to calculate what percentage of total project costs the cost of each individual task represents.

The Formula Properties dialog, which you use to create formulae, makes it easy to insert different elements into formulae, and checks the syntax of your formulae as you construct them, warning you of any problems as soon as they occur. It is possible to construct simple formulae - for example, to display the results of subtracting the contents of one field from another - very easily. However, you can also construct much more powerful formulae, using a formula-writing language that has its own syntax. This Help contains a number of sample formulae. You may find it useful to base your initial formulae on these examples, changing different elements of the sample formulae in order to build up a greater understanding of the formula-writing language.

Filtering on the results of formulae

You can create filters that filter items according to the results of formulae:

If a formula returns a number of some description (for example, a cost or a duration), you can filter for items for which the formula returns a number that is equal to, greater than, greater than or equal to, less than, or less than or equal to a specified number.
If a formula returns a date, you can filter for items for which the formula returns a date that is equal to, greater than, greater than or equal to, less than, or less than or equal to a specified date.
If a formula returns an alphanumeric string, you can filter for items for which the formula returns a specified string.
If a formula returns a result that is a boolean (ie yes/true or no/false), you can filter for items for which the formula returns True or False.
If a formula returns a result that is a traffic light indicating None, Low, Medium or High, you can filter for items for which the formula returns any of those values.