Example formulae

This simple example formula - actually one of the simplest formulae that can be constructed - displays a Name column that cannot be edited. One example of a situation in which this might be useful is if you want to set up a progress entry view in which users can edit the % complete column in the spreadsheet for each task, but not the Name column. If you were to simply include the standard Name column in the spreadsheet, it would be editable; creating a formula to display the name of each task results in a read-only Name column.

1. On the Formula Properties dialog, click the Field button. A menu appears, from which you can choose the field that you want to insert into the formula.
2. Click the Identification submenu, then click the Name field. The Select Object dialog appears. You use this dialog to specify the object about which you want the field to display information.
3. For this simple formula, click OK without editing the dialog's default settings. The Formula field on the Formula Properties dialog displays the simple formula that has resulted from selecting the Name field:
   
   
   
   Note that the Status field displays 'OK', indicating that the formula is valid.
4. Click OK to close the Formula Properties dialog.

You can follow the instructions above to create a formula that displays a read-only version of any field. For example, to create a formula that displays a read-only Start column, follow the instructions above, but instead of selecting Name from the Identification submenu in step 2, select Start from the Date submenu.

This formula illustrates how to refer to data from specific tasks on bars that contain more than one task. It displays the duration between the first and second tasks on each bar. In order to do this, you need to construct a formula that subtracts the start date of the first task on each bar from the start date of the second task.

1. Firstly, you need to insert the start date of the second task on each bar into the formula. On the Formula Properties dialog, click the Field button. A menu appears.
2. Click the Date submenu, then click the Start field. The Select Object dialog appears.
3. Select Task in the Object to evaluate for field, click the Use by order radio button and select 2nd in the field to the right of the radio button to indicate that you want to refer to the start date of the second task on each bar, then click OK. The Formula field on the Formula Properties dialog displays the formula as it stands so far:
   
   
   
   Task[>2].Start indicates that the formula will evaluate the start date of the second task on each bar.
4. You now need to insert a minus symbol into the formula. Click the Symbol button. A menu appears, from which you can choose the symbol that you want to insert into the formula.
5. Within the Arithmetic section of the menu, click -. A minus sign appears in the formula:
   
   
   
   Note that the Status field now displays an error and that the location of the error in the formula itself is highlighted with red underlining. The error is caused by the formula being incomplete.
   
   Note also that as an alternative to selecting the minus sign from the Symbol menu, you can type it directly into the Formula field; once you are comfortable with constructing formulae, you can type any element of a formula directly into the Formula field.
6. Finally, you need to insert the start date of the first task on each bar into the formula. Click the Field button, click the Date submenu, then click the Start field. The Select Object dialog appears.
7. Select Task in the Object to evaluate for field, click the Use by order radio button and select 1st in the field to the right of the radio button to indicate that you want to refer to the start date of the first task on each bar, then click OK. The Formula field on the Formula Properties dialog now displays the complete formula, which - as the Status field confirms - is now valid:
   
   
8. Click OK to close the Formula Properties dialog.

Note that in order to make formulae easier to read, you can insert space characters - using the SPACE bar on your keyboard - between the various elements. In this example formula, it would be good practice to insert space characters on either side of the minus sign, as illustrated below:

All illustrations of the following example formulae in this topic will include such space characters in suitable places.

This formula illustrates how to refer to data from the summary task in which each task is located. It displays the duration of each task as a percentage of the duration of its parent summary. In order to do this, you need to construct a formula that divides the duration of each task by the duration of its summary task, then multiplies the result by 100 to present it as a percentage.

1. Firstly, you need to insert the Duration field into the formula. On the Formula Properties dialog, click the Field button. A menu appears.
2. Click the Date submenu, then click the Duration field. The Select Object dialog appears. Click OK to close it.
3. Insert a division symbol into the formula, either by selecting it from the Arithmetic section of the Symbol menu or by typing it directly into the formula. The formula should now look as follows:
   
   
4. You now need to insert the duration of each task's summary into the formula. Click the Field button, click the Date submenu, then click the Duration field. The Select Object dialog appears.
5. Select Summary in the Object to evaluate for field, then click OK. The Formula field on the Formula Properties dialog displays the formula as it stands so far:
   
   
   
   Summary.Duration indicates that the formula will evaluate the duration of each task's parent summary.
6. As it stands, the formula will return a fraction. In order to display the duration of each task as a percentage of the duration of its parent summary, add opening and closing parentheses around the formula - either by selecting them from the Miscellaneous section of the Symbol menu or by typing them directly into the formula - and type * 100 after the closing parenthesis:
   
   
7. Click OK to close the Formula Properties dialog.

Making a small change to the formula - selecting Project rather than Summary in step 5 above - would display the duration of each task as a percentage of its parent project:

Selecting Programme rather than Summary in step 5 above would display the duration of each task as a percentage of the overall programme of projects:

This formula is similar to the example above, but rather than displaying the duration of each task as a percentage of the duration of its parent summary in the live data, it displays it as a percentage of the duration of its parent summary in the current baseline. In order for this formula to work, you must have a baseline that has been set to 'Current'.

1. On the Formula Properties dialog, insert the Duration field into the formula, followed by a division symbol:
   
   
2. You now need to insert the duration of each task's summary in the current baseline into the formula. Click the Field button, click the Date submenu, then click the Duration field. The Select Object dialog appears.
3. Select Summary in the Object to evaluate for field, select CB Current Baseline in the Project or baseline field, then click OK. The Formula field on the Formula Properties dialog displays the formula as it stands so far:
   
   
   
   Summary[~CB].Duration indicates that the formula will evaluate the duration of each task's parent summary in the current baseline, rather than in the live data.
4. As in the above example, complete the formula by adding opening and closing parentheses around the formula and typing * 100 after the closing parenthesis:
   
   
5. Click OK to close the Formula Properties dialog.

This formula illustrates one way of using variable dates within formulae. It displays the duration between today's date - a variable date - and the start date of each task. One example of a situation in which this might be useful is if you want to enable resources to see how long they have before they need to start work on a task.

1. On the Formula Properties dialog, insert the Start field into the formula, followed by a minus symbol:
   
   
2. You now need to insert the variable date of today's date into the formula. Click the Symbol button, click the Variable Date submenu, then click the Today variable date. The completed formula should now look as follows:
   
   
3. Click OK to close the Formula Properties dialog.

This formula is similar to the example above, but it displays the duration in hours without a time unit being displayed. You might find it useful to omit the time unit from the results of such a formula if you need to export the data to an external application - for example Microsoft Excel® - via a tabular report.

1. As in the example above, on the Formula Properties dialog, insert the Start field, followed by a minus symbol, followed by the variable date of Today into a formula:
   
   
2. Add opening and closing parentheses around the formula - either by selecting them from the Miscellaneous section of the Symbol menu or by typing them directly into the formula - and type / '1h' after the closing parenthesis:
   
   
   
   / '1h' converts the resulting duration into hours, according to whichever calendar is applied to each task. If you wanted to convert the duration into days, you could enter / '1d' instead.
3. Click OK to close the Formula Properties dialog.

The formula in the above example would probably return many results that are not whole numbers (integers), for example 96.34 or 15.89. To prevent this, and to simplify the information that is returned by the above formula, you could round the results to whole numbers as follows.

1. On the Formula Properties dialog, enter the formula given in the example above:
   
   
2. Click in the Formula field at the start of the formula.
3. Click the Function button, click the Mathematical submenu, then click the Round(value, decimal_places) function. The formula should now look as follows:
   
   
   
   You now need to replace the value parameter of the Round function with the formula as it stood previously.
4. Click and drag the cursor over the (Start - Today) / '1h' section of the formula to select it, then right-click the selected text and select Cut from the menu that appears:
   
   
5. Click and drag the cursor over the value section of the formula to select it, then right-click the selected text and select Paste from the menu that appears:
   
   
6. Finally, specify the number of decimal places to which to round the formula results by selecting the decimal_places section of the formula and overtyping it with 0. The completed formula should now look as follows:
   
   
7. Click OK to close the Formula Properties dialog.

This formula illustrates one way of manipulating dates. It displays the date one week prior to the start date of each task. One example of a situation in which this might be useful is if you need to order materials one week before the task for which they are required starts. This formula would return the date on which the materials need to be ordered.

1. On the Formula Properties dialog, insert the Start field into the formula, followed by a minus symbol:
   
   
2. To complete the formula, you need to subtract a week from the start date. Type - '1w' at the end of the formula:
   
   
3. Click OK to close the Formula Properties dialog.

This formula illustrates one way of manipulating cost data. It calculates and displays the VAT element of costs - assuming a VAT rate of 20%.

1. On the Formula Properties dialog, click the Field button, click the Cost or Income submenu, then click the Cost field. The Select Object dialog appears.
2. Click OK without editing the dialog's default settings. The Formula field on the Formula Properties dialog displays the formula as it stands at the moment:
   
   
3. To display the VAT element of costs, we need to divide the cost by 100, then multiply by the current rate of VAT - 20 in this case. Add opening and closing parentheses around the Cost field - either by selecting them from the Miscellaneous section of the Symbol menu or by typing them directly into the formula - type / 100 before the closing parenthesis, then type * 20 after the closing parenthesis:
   
   
4. Click OK to close the Formula Properties dialog.

This formula illustrates another way of manipulating cost data. It calculates and displays the cost of each task as a percentage of the cost of the programme.

1. On the Formula Properties dialog, insert the Cost field into the formula, followed by a division symbol:
   
   
2. You now need to insert the cost of the programme into the formula. Click the Field button, click the Cost or Income submenu, then click the Cost field. The Select Object dialog appears.
3. Select Programme in the Object to evaluate for field, then click OK. The Formula field on the Formula Properties dialog displays the formula as it stands so far:
   
   
   
   Programme.Cost indicates that the formula will evaluate the cost of the programme of projects, rather than the cost of each task.
4. Add opening and closing parentheses around the formula, then and type * 100 after the closing parenthesis:
   
   
5. Now complete the formula by typing & "%" at the end. This will display a percentage sign at the end of the formula results:
   
   
6. Click OK to close the Formula Properties dialog.

This formula illustrates another way of manipulating cost data. It displays the cost of each task from a specific task centre only, which can be useful if you have more than one cost centre assigned to each task.

1. On the Formula Properties dialog, click the Field button, click the Cost or Income submenu, then click the Cost field. The Select Object dialog appears.
2. Select the cost centre for which you want to display costs in the Cost centre field, then click OK. The completed formula should now look as follows:
   
   
   
   The parameters that have been added to the Cost field indicate that the formula will evaluate the cost of the cost centre with an internal ID of 2552, rather than the cost of all cost centres. Note that in your project, the internal ID of the selected cost centre will probably be different.
3. Click OK to close the Formula Properties dialog.

This formula illustrates one way of manipulating resource data. It calculates and displays the percentage by which effort on each task has increased or reduced in comparison with the tasks' effort in the current baseline. Expressed as a calculation, this is the difference between planned effort and actual effort, divided by planned effort and expressed as a percentage.

1. On the Formula Properties dialog, click in the Formula field and add an opening parenthesis to the formula.
2. Click the Field button, click the Resource submenu, then click the Effort variance field. The Select Object dialog appears. Click OK. The formula should now look as follows:
   
   
3. Add a division symbol after the formula.
4. Click the Field button, click the Resource submenu, then click the Effort field. The Select Object dialog appears.
5. Select CB Current Baseline in the Project or baseline field, then click OK. The formula should now look as follows:
   
   
6. Complete the formula by adding a closing parenthesis, then typing * 100 & "%" to indicate that you want to multiply the result by 100 and display a percentage sign at the end of the formula results:
   
   
7. Click OK to close the Formula Properties dialog.

This formula illustrates one way of using conditions in formulae. It scans the contents of each task's Notes field to ascertain whether it contains the word "delay". If the word is found for a task, the formula displays it; if the word is not found, the formula displays the word "OK". This formula uses an If function, which enables you to construct formulae that do one thing if a condition is met, and another thing if the condition is not met.

1. On the Formula Properties dialog, click the Function button, click the Conditional submenu, then click the If(expression1, expression2, expression3) function. The formula should now look as follows:
   
   
   
   You need to replace the expression1 parameter of the function with the condition, the expression2 parameter with the action to take if the condition is met and the expression3 parameter with the action to take if it is not met.
2. Click and drag the cursor over the expression1 parameter to select it, then press DELETE on the keyboard.
3. Click the Function button, click the Text submenu, then click the Search(find_text, within_text) function. The formula should now look as follows:
   
   
4. Select the find_text parameter and overwrite it with the text that you want to search for, enclosed within double quotation marks - "delay":
   
   
5. Select the within_text parameter, then click the Field button, click the Identification submenu and click Notes. Click OK to close the Select Object dialog when it appears. The formula should now look as follows:
   
   
6. Select the expression2 parameter and overwrite it with "Delay", to indicate that this word should be displayed if the word "delay" is present in a task's Notes field.
7. Select the expression3 parameter and overwrite it with "OK", to indicate that this word should be displayed if the word "delay" is not present in a task's Notes field. The completed formula should look like this:
   
   
8. Click OK to close the Formula Properties dialog.

This formula illustrates an alternative way of using conditions in formulae. It scans the contents of each task's Notes field to ascertain whether it contains the word "delay". If the word is found for a task, the formula displays a red traffic light indicator; if the word is not found, a green traffic light indicator is displayed.

1. On the Formula Properties dialog, click the Function button, click the Conditional submenu, then click the If(expression1, expression2, expression3) function. The formula should now look as follows:
   
   
   
   You need to replace the expression1 parameter of the function with the condition, the expression2 parameter with the action to take if the condition is met and the expression3 parameter with the action to take if it is not met.
2. Click and drag the cursor over the expression1 parameter to select it, then press DELETE on the keyboard.
3. Click the Function button, click the Text submenu, then click the Search(find_text, within_text) function. The formula should now look as follows:
   
   
4. Select the find_text parameter and overwrite it with the text that you want to search for, enclosed within double quotation marks - "delay":
   
   
5. Select the within_text parameter, then click the Field button, click the Identification submenu and click Notes. Click OK to close the Select Object dialog when it appears. The formula should now look as follows:
   
   
   
   You now need to overwrite the expression2 and expression3 parameters with the traffic light indicators that should be displayed in each case. These are reserved words within each formula: High displays a red traffic light, Medium displays amber, Low displays green and None displays grey.
6. Select the expression2 parameter and overwrite it with High, to indicate that a red traffic light should be displayed if the word "delay" is present in a task's Notes field.
7. Select the expression3 parameter and overwrite it with Low, to indicate that a green traffic light should be displayed if the word "delay" is not present in a task's Notes field. The completed formula should look like this:
   
   
8. Click OK to close the Formula Properties dialog.

This formula illustrates an a way of using conditions in formulae using the Switch function, which - unlike the If function - can handle more than two alternative outcomes. This formula displays a red traffic light indicator if a task's cost is between 200 and 300, an amber traffic light if it is between 100 and 200 and a green traffic light if it is less than 100.

1. On the Formula Properties dialog, click the Function button, click the Conditional submenu, then click the Switch(test1, expression1, test2, expression2, ...) function. The formula should now look as follows:
   
   
   
   You need to replace the test1 parameter of the function with the first condition, the expression1 parameter with the action to take if that condition is met, the test2 parameter with the second condition, the expression2 parameter with the action to take if that condition is met, and so on.
2. Click and drag the cursor over the test1 parameter to select it, then press DELETE on the keyboard.
3. Click the Field button, click the Cost or Income submenu, then click the Cost field. Click OK to close the Select Object dialog when it appears. The formula should now look as follows:
   
   
4. As the cost field is displayed as a string, you need to translate it into a numerical value. You can do this by dividing it by £1: type / '£1' after the Cost field in the formula:
   
   
5. To complete the first condition, type < 100 after / '£1'. The complete condition should now be Cost / '£1' <100:
   
   
6. You now need to overwrite the expression1 parameter with the traffic light indicator that should be displayed if the first condition is met. You want a green traffic light to be displayed in this case, so select the expression1 parameter and overwrite it with Low:
   
   
7. You now need to overwrite the test2 parameter with the second condition: one that tests whether the cost is between 100 and 200. Select the test2 parameter and overwrite it with Cost / '£1' >= 100 and Cost / '£1' < 200:
   
   
8. Overwrite the expression2 parameter with the traffic light indicator that should be displayed if the second condition is met. You want an amber traffic light to be displayed in this case, so select the expression2 parameter and overwrite it with Medium:
   
   
9. You now need to complete the formula by adding your own third test and expression, which will display a red traffic light if the cost is between 200 and 300. Overwrite the ... with Cost / '£1' >= 200 and Cost / '£1' < 300, High. The completed formula should look as follows:
   
   
10. Click OK to close the Formula Properties dialog.

You could simplify this formula by using Let to define a local variable and reusing the local variable in the formula. An alternative, using Let, is illustrated below:

Note also in the example above how using line breaks and spaces at the start of lines in a formula makes it easier to read. You can add line breaks to formulae wherever you like by pressing ENTER.

The example formula above, which uses the Switch function to display a red traffic light indicator if a task's cost is between 200 and 300, an amber traffic light if it is between 100 and 200 and a green traffic light if it is less than 100, displays nothing if a task has a cost that is greater than or equal to 300. This can be seen as an error in the formula itself, as it fails to cater for all possible situations. Other formulae may generate errors. For example, you could construct a formula that referred to a user-defined field that did not exist, one that referred to a cost centre that was not allocated to a task, or one that divided cost by planned cost in circumstances where the planned cost may be zero (attempting to divide anything by zero returns an error).

It is possible to construct formulae in such a way that something - rather than nothing - is displayed if an error such as this occurs. You do this using the Try function, which enables you to specify what should be displayed if the formula returns nothing. In its simplest form, the Try function takes two parameters: the formula itself, and the action that should be taken if an error occurs.

This example formula illustrates the use of the Try function by taking the example above and enclosing it within the Try function, indicating that a red traffic light indicator should be displayed if the cost of a task is outside the parameters of the formula.

1. On the Formula Properties dialog, enter the formula given in the example above:
   
   
2. Click in the Formula field at the start of the formula.
3. Click the Function button, click the Error Handling submenu, then click the Try(expression, <alternative>) function. The formula should now look as follows:
   
   
   
   You now need to replace the expression parameter of the Try function with the formula as it stood previously.
4. Click and drag the cursor over the entire formula from Switch(Cost onwards to select it, then right-click the selected text and select Cut from the menu that appears.
5. Click and drag the cursor over the expression section of the formula to select it, then right-click the selected text and select Paste from the menu that appears. The section of the formula that you cut is pasted over the expression parameter as follows:
   
   
6. Now overwrite the <alternative> parameter with High, to indicate that a red traffic light should be displayed if the cost of a task is outside the parameters of the formula. The completed formula should now look as follows:
   
   
7. Click OK to close the Formula Properties dialog.

You could simplify this formula by using Let to define a local variable and reusing the local variable in the formula. An alternative, using Let, is illustrated below:

Note also in the example above how using line breaks and spaces at the start of lines in a formula makes it easier to read. You can add line breaks to formulae wherever you like by pressing ENTER.

This formula displays the cost of each task from the cost centre with an object ID of 30, minus the income of each task from the same cost centre.

This formula displays the income in the live data minus the income that is recorded in the that current baseline for each task. In order for this formula to work, you must have a baseline that has been set to 'Current'.

This formula uses the Value function to modify the work character strings for the permanent resources with IDs of 1074 and 1075 into numeric values, adds them together and displays the result.

This formula displays the combined costs from two specific cost centres once a 5% discount has been applied to them for each task.

This formula displays the cost of each task, divided by the number of days in the tasks' duration.

In order for this formula to work, you must have set up a permanent scheduled allocation-level user-defined field called CostPerDayFactor, an integer-type field that is used to store the cost per day of each allocation, expressed in £ per day.

This formula displays a red traffic light indicator if a task's cost is greater than its income and a green traffic light if the cost is less than or equal to the income.

The following formula produces an identical result:

In order for this formula to work, you must have set up the following bar-level user-defined fields:

• OrderPlaced, a boolean-type field that is used to record whether or not an order has been placed.
• DelTime, a duration-type field that is used to store the delivery time in days.

This formula displays a red traffic light indicator if a task's delivery date falls on or earlier than yesterday, an amber traffic light indicator if it falls today, a green traffic light indicator if it falls on or later than tomorrow, and a grey traffic light indicator if an order has actually been placed.

You could simplify this formula by using Let to define a local variable and reusing the local variable in the formula. An alternative, using Let, is illustrated below:

Note also in the example above how using line breaks and spaces at the start of lines in a formula makes it easier to read. You can add line breaks to formulae wherever you like by pressing ENTER.

This formula displays the cost of each task from either the cost centre with an object ID of 30 or the cost centre with an object ID of 1080, whichever is the highest.

This formula displays the cost of each task from either the cost centre with an object ID of 30 or the cost centre with an object ID of 1080, whichever is the lowest.

This formula displays the duration between the finish date of each task and the finish date of the project.

This formula displays the number of days between the finish date of each task and the finish date of the project.

This formula displays the percentage of the total cost of each task that relates to the cost centre with an object ID of 1080.

This formula displays the quantity of a resource that has been used, based on the percentage complete of the corresponding task. Note that the PercentComplete field needs to be divided by 100 in order to be used in this way, as it is treated as an integer in formulae. For example, a value of 50% complete would be translated into an integer of 50, so we need to divide that by 100 before multiplying it by the Quantity field in order to end up with a the correct figure. If a task was 50% complete and the Quantity field had a value of 1,000, this formula would return a value of 500.