To create a new formula:
- Open Library Explorer.
- Open the Formula library. All existing formulae appear in the list view.
- Right-click a blank area of the list view and select New Formula. A new formula appears in the list, with the cursor flashing in the Name column.
- Enter a name for the formula and press Enter.
- Right-click the formula and select Properties. The Formula Properties dialog appears.
- Enter a name for the formula in the Name field and enter a more detailed description in the Description field.
- If you want to assign a category to the formula, select a formula category in the Category field. Categorising formulae makes it easier to find the formula you are looking for, for example when selecting a formula to display in a spreadsheet column. Whenever you select a formula, they are arranged into their categories, with any formulae that do not belong to a category being displayed below the formula categories that do exist.
- Construct the formula itself in the Formula field. You can type a complete formula directly into the field; you can use the Insert buttons to insert specific elements into a formula, and you can construct formulae using a combination of direct typing and using the Insert buttons. Note that you can insert spaces between the various elements of a formula to aid readability, provided that the spaces do not appear in the middle of individual elements.
You may find it useful to base your formulae on some of the sample formulae that are provided in this Help file.
The following table indicates which types of elements you can use each button to insert into formulae. Click the text in the Use to insert column to access a topic that gives more details of each type of element:
Button Use to insert Examples
+,-, *, /
=, <>, >, >=, <, <=
&, And, Or, Not
Yesterday, Today, CurrentProgressPeriodDate, WeekStart
High, StartOnOrAfter, ASAP, Waiting
CB, LP, PB
Search(find_text, within_text), If(expression 1, expression 2, expression 3)
Spreadsheet fields; the Select Object dialog appears once you have selected a field for you to specify the object about which you want the field to display information
Name, Cost, Start, Task.ActualDuration
References to specific objects (useful in the parameters that are required for various functions); the Select Object dialog appears for you to specify the object that you want to use as a function parameter
You can simplify complex formulae by defining one or more "local variables" in your formulae.
- As you construct a formula, the Status field alerts you to any potential errors in the formula syntax. Click the button to the right of the Status field to view additional information on any error messages that are displayed.
- Click OK to save the formula once you have finished editing it. If you have edited an existing formula and want to save it as a new formula - ie without overwriting the existing formula - enter a new name for the formula in the Name field and select the Save as new formula check box before clicking OK.
- Close Library Explorer.
You can now configure a spreadsheet column - or a table definition - to display the results of the formula.
The table below provides a list of the messages that can be displayed in the Status field as you create formulae. Click a message to display more information.