Creating formulae

To create a new formula:

  1. Open Library Explorer.
  2. Open the Formula library. All existing formulae appear in the list view.
  3. 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.
  4. Enter a name for the formula and press Enter.
  5. Right-click the formula and select Properties. The Formula Properties dialog appears.
  6. Enter a name for the formula in the Name field and enter a more detailed description in the Description field.
  7. 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.
  8. 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:
    ButtonUse to insertExamples


    Arithmetic symbols

    +,-, *, /

    Comparison symbols

    =, <>, >, >=, <, <=

    Logic symbols

    &, And, Or, Not

    Miscellaneous symbols

    (), &

    Variable dates

    Yesterday, Today, CurrentProgressPeriodDate, WeekStart

    Enumerated values

    High, StartOnOrAfter, ASAP, Waiting

    Baseline references

    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[2383], 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

    [1045], [3456]

    You can simplify complex formulae by defining one or more "local variables" in your formulae.
  9. 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.
  10. 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.
  11. Close Library Explorer.

You can now configure a spreadsheet column - or a table definition - to display the results of the formula.

Related Topics:

Categorising formulae

Setting up spreadsheet columns to display the results of formulae

The components of a formula

Simplifying complex formulae using local variables

Example formulae