Formula symbols explained

You insert symbols into formulae using the Symbol button on the Formula Properties dialog. A menu appears, from which you can choose the symbol that you want to insert into the formula.

Arithmetic symbols

You insert these symbols into formulae to carry out mathematical functions on two fields or values.

+

Description Adds two values together.
Example

Cost(30) + Cost(1080)

 

Sums the costs related to the cost centre with an object ID of 30 and the costs related to the cost centre with an object ID of 1080.

-

Description Subtracts the second value from the first.
Example

Start - '1w'

 

Subtracts one week from the start date of an object.

Notes As well as being used to subtract one value from another, the - symbol can also be used to represent negative values, for example -1; -'£30', '-£30', '£-30'; -'2d 3h', '-2d 3h'. Note the various positions in which the - symbol can appear, all of which are valid.

*

Description Multiplies two values together.
Example

Cost * 0.2

 

Multiplies costs by 0.2, effectively returning the amount of tax involved in a cost where the tax rate is 20%.

/

Description Divides the first value by the second.
Example

Cost / 20

 

Divides costs by 20, effectively returning the amount of tax involved in a cost where the tax rate is 20%.

Comparison symbols

You insert these symbols into conditional formulae to compare two fields or values.

=

Description Tests whether two values are equal.
Example

If(Task.Name = "Roofing", High, None)

 

A traffic light indicator that displays high priority for all tasks called "Roofing", and no priority for all other tasks.

<>

Description Tests whether two values are different.
Example

If(TaskCodeLibrary(4173) <> "2 Bed Terraced", None, High)

 

A traffic light indicator that displays high priority for all tasks to which the "2 Bed Terraced" code has been assigned from the code library that has an object ID of 4173, and no priority for all other tasks.

>

Description Tests whether the first value is greater than the second.
Example

If(Search("issue", Notes) > 0, High, None)

 

A traffic light indicator that displays high priority for all tasks with notes that contain the word "issue", and no priority for all other tasks.

>=

Description Tests whether the first value is greater than or equal to the second.
Example

If([~CB].Income >= Income, High, Low)

 

A traffic light indicator that displays high priority for all tasks with projected income in the current baseline that is greater than or equal to the income in the live data, and low priority for all other tasks.

<

Description Tests whether the first value is less than the second.
Example

Try(Switch(Income < Cost, High), Low)

 

A traffic light indicator that displays high priority for all tasks with costs that exceed income, and low priority for all other tasks.

<=

Description Tests whether the first value is less than or equal to the second.
Example

Try(Switch(Income <= [~PB].Income, High), Low)

 

A traffic light indicator that displays high priority for all tasks with income in the live data that is less than or equal to projected income in the project baseline, and low priority for all other tasks.

Logic symbols

You insert these symbols into conditional formulae to specify the condition, or conditions, that must be met.

AND

Description Joins two conditions together and indicates that both conditions must be met.
Example

If((Search("issue", Notes) > 0) AND (TaskCodeLibrary(4173) = "2 Bed Terraced"), High, None)

 

A traffic light indicator that displays high priority for all tasks with notes that contain the word "issue" and to which the "2 Bed Terraced" code has been assigned from the code library that has an object ID of 4173, and no priority for all other tasks.

OR

Description Joins two conditions together and indicates that either condition must be met.
Example

If((Search("issue", Notes) > 0) OR ([~CB].Income >= Income), High, Low)

 

A traffic light indicator that displays high priority for all tasks that either have notes that contain the word "issue", or with projected income in the current baseline that is greater than or equal to the income in the live data, and low priority for all other tasks.

NOT

Description Tests whether a condition is not met.
Example

If((Search("issue", Notes) > 0) AND NOT (TaskCodeLibrary(4173) = "2 Bed Terraced"), High, None)

 

A traffic light indicator that displays high priority for all tasks with notes that contain the word "issue" and to which the "2 Bed Terraced" code has not been assigned from the code library that has an object ID of 4173, and no priority for all other tasks.

Miscellaneous symbols

These are additional characters that can be used in formulae.

()

Description Opening and closing parenthesis, used (always in a pair) to specify the order in which expressions within formulae are evaluated. Enclosing an inner expression within parentheses ensures that it is completely evaluated before the outer expression.
Example

If(Search("issue", Notes) > 0, High, None)

 

A traffic light indicator that displays high priority for all tasks with notes that contain the word "issue", and no priority for all other tasks.

Notes

In the absence of any parentheses, calculations are carried out in the following order of precedence:

  1. Negation (as in -1).
  2. Multiplication and division using * and /.
  3. Addition and subtraction using + and -.
  4. The concatenation of strings using &.
  5. Comparison using =, <>, <, <=, > and >=.

For example, the following formula produces a result of 11 rather than 21 because the multiplication is carried out before the addition:

 

5+2*3

 

If parentheses were added to this formula, changing it to (5+2)*3, it would produce a result of 21 rather than 11. This is because the addition of parentheses to the formula means that the expression that is contained within the parentheses is evaluated before the rest of the formula, overriding the normal order of precedence.

&

Description Concatenates two strings.
Example

Cost / UDF("CostPerDayFactor") & " Days"

 

The cost of a task divided by a user-defined field that contains the cost per day, with the text string " Days" appended to the resulting value.

Variable dates

All of the variable dates that are available for use in Powerproject, for example Today, Tomorrow, Month Start and Year End, can be used within formulae. There are many uses of variable dates within formulae, some examples of which are displayed below. Note that you must remove all spaces from the names of variable dates when using them in a formula.

Example 1

Task.Start - Today

 

The number of days from today until the start date of a task.

Example 2

Try(Switch
(UDF("OrderPlaced"), None,
Start - (UDF("DeliveryTimeInDays") * '1d') <= Yesterday, High,
Start - (UDF("DeliveryTimeInDays") * '1d') = Today, Medium,
Start - (UDF("DeliveryTimeInDays") * '1d') >= Tomorrow, Low),
None)

 

A traffic light indicator that displays the urgency of orders. In this example, "OrderPlaced" and "DeliveryTimeInDays" are user-defined fields that store data at bar level; "OrderPlaced" is a date-type field and "DeliveryTimeInDays" is a float-type field.

Note that statements such as Today + '1.5d' or Tomorrow - '2w' are supported within formulae, provided that you enclose the duration constants within single quotation marks, as illustrated above.

Available variable dates

Enumerated values

The results of several spreadsheet fields can differ depending on which language version of Powerproject you are using. For example, the possible results of the Priority field in an English version of Powerproject are None, Low, Medium and High; in a German version of Powerproject the results of this field would be Keine Priorität, Niedrig, Normal and Hoch.

If you were to refer to the results of these fields in formulae by enclosing the text of the results in double quotation marks (for example, if you were to refer to the results of the Priority field as "High" or "Low"), your formula would not work in a foreign language version of Powerproject. To get around this problem, you should refer to the results of these fields using 'enumerated values'. Each possible result of the affected fields has a corresponding enumerated value - which in many cases, is the same as the English version of the results. For example, the Waiting State field gives results that are normally displayed in the spreadsheet as Waiting and Can Start. The enumerated values that correspond to these results are Waiting and CanStart. In addition to this, enumerated values can be used as parameters or return values from a number of functions.

Two uses of enumerated values are displayed below:

Example 1

If((Constraint = StartOn), High, None)

 

A traffic light indicator that displays high priority for all tasks to which a Start On constraint flag has been applied, and no priority for all other tasks.

Example 2

If((Search("ground", Task.Name) > 0) AND (ProcessState = NotStarted), High, None)

 

A traffic light indicator that displays high priority for all tasks that contain the word "ground" in their name and that have not yet started, and no priority for all other tasks.

The following tables display the various enumerated values that are available:

Enumerated values for the Priority field

Priority field results Corresponding enumerated values
None None
Low Low
Medium Medium
High High

Enumerated values for the Constraint field

Constraint field results Corresponding enumerated values
None None
Start On StartOn
Start On Or Before StartOnOrBefore
Start On Or After StartOnOrAfter
Finish On FinishOn
Finish On Or Before FinishOnOrBefore
Finish On Or After FinishOnOrAfter
Work Between WorkBetween
Deadline Deadline

Enumerated values for the Placement field

Placement field results Corresponding enumerated values
ASAP ASAP
ALAP ALAP
ASAP Force Critical ASAPForceCritical

Enumerated values for the Process State field

Process State field results Corresponding enumerated values
Not Started NotStarted
In Progress InProgress
Completed Completed

Enumerated values for the Waiting State field

Waiting State field results Corresponding enumerated values
Waiting Waiting
Can Start CanStart

Enumerated values for the Type field

Type field results Corresponding enumerated values
Bar BarType
Task TaskType
Milestone MilestoneType
Expanded Task ExpandedTaskType
Hammock Task HammockTaskType
Permanent Scheduled Allocation PermanentScheduledAllocationType
Cost Allocation CostAllocationType
Consumable Scheduled Allocation ConsumableScheduledAllocationType
Permanent Resource PermanentResourceType
Perm Resource Skill PermResourceSkillType
Cost Centre CostCentreType
Consumable Resource ConsumableResourceType
Permanent Demand Allocation PermanentDemandAllocationType
Consumable Demand Allocation ConsumableDemandAllocationType
Task Completed Section TaskCompletedSectionType
Allocation Completed Section AllocationCompletedSectionType

Enumerated values for the Subtype field

Subtype field results Corresponding enumerated values
Scheduled ScheduledType
Demand DemandType
Pending Demand PendingDemandType
Cost CostType
Income IncomeType
Summary SummaryType
Chart ChartType
Start StartType
Finish FinishType

Enumerated values for the ContextType function

The enumerated values in the following table do not relate to a spreadsheet field; they relate to the results of a function - called ContextType - that you can use in formulae to ascertain the type of object.

More information on the ContextType function

ContextType function results Corresponding enumerated values
Allocation AllocationContext
Task TaskContext
Bar BarContext
Sort Band SortBandContext

Baseline reference symbols

You insert these symbols into formulae to make reference to values from a baseline rather than from the live data.

PB

Description Refers to the project baseline.
Example

Try(Switch(Income <= [~PB].Income, High), Low)

 

A traffic light indicator that displays high priority for all tasks with income in the live data that is less than or equal to projected income in the project baseline, and low priority for all other tasks.

CB

Description Refers to the current baseline.
Example

(Actualcost / [~CB].Cost) * 100 & "%"

 

Shows the actual costs as a percentage of the costs that were planned in the current baseline.

LP

Description Refers to the live data; it is not usually necessary to use this symbol, as formulae refer to the live data by default unless you specify otherwise by referring to a baseline.
Example

If((Search("issue", Notes) > 0) OR ([~CB].Cost <= [~LP].Cost), High, Low)

 

A traffic light indicator that displays high priority for all tasks that either have notes that contain the word "issue", or with projected costs in the current baseline that is less than or equal to the costs in the live data, and low priority for all other tasks.

Related Topics:

The components of a formula

Formula functions explained

Formula fields explained

Formula objects explained

Creating formulae

Example formulae