Use formulas |
What do you want to do? |
Formulas let you calculate
the value of a topic property by evaluating an expression that
uses other topic properties from specific topics, task attributes,
or a range of topics on your map. The properties used in the calculation
are called input properties, and the resulting property is called
a calculated property. Any topic on your map can contain one or
more formulas to define one or more calculated properties.
Calculated properties are updated dynamically, so their values
are automatically updated as you add or edit the input properties,
or move, add or remove their topics.
Formulas can use input properties whose type is number, currency,
integer, percentage or calculated.
 Read
Note
|
|
What is a formula?
Example of a simple formulas are:
[unit_cost]
= 22 + 3
Meaning: the "unit_cost"
topic property is equal to 22 plus 3.
[unit_price]
= [unit_cost]
* 1.25
Meaning: the "unit_price"
topic property for this topic equals the "unit_cost"
property times 1.25.
[product_profit]
= ([unit_price] - [unit_cost]) *[units]
Meaning: the "product_profit"
topic property equals the "unit_price"
property minus the "unit_cost"
property times the number of units.
You can use topics anywhere on the map in the calculation, by using
a formula like this:
[total_profit]
= SUM
( MapTopics
[product_profit])
Meaning: the
"total_profit" topic
property for this topic equals the sum
of the "product_profit"
properties of all topics in all map topics.
You can also create more complex calculations that can evaluate a range
of topics anywhere on the map and include only specific topics or topic
types.
An example of a more sophisticated formula that you can create in the
Formula Editor is:
[total_budget_surplus]
= SUM(MapTopics
[budget])
- SUM (MapTopics[travel_expense],MapTopics[equipment_expense],MapTopics[salaries],topic[operations_expense])
Meaning: the "total_budget_surplus"
topic property for this topic equals the sum
of the "budget"
property on all map topics, minus the
sum of the "travel_expense",
"equipment_expense",
and "salaries" properties
on all map topics, and the "operations_expense" property
on a specific map topic.
How do I create formulas?
Use the buttons on the Advanced
tab to enter formulas: AutoCalc
provides an interface for easy formula creation, or click Formulas
to use the Formula Editor to create or edit more complex formulas.
Read Note
Create
a simple formula with AutoCalc
Autocalc lets you create simple formulas that include a single function,
one input property, and one range.
To create more sophisticated formulas, use the Formula
Editor:
Select a topic on the map.
On the ribbon's Advanced
tab, click AutoCalc.
In the dialog, select:
The
calculated property to be created on this topic by the formula.
The
Function to use in the calculation.
The
input property used by the function.
The
topic range to evaluate.
Select the checkbox to add the
input property to topics that are used in the calculation but do not
already contain this property. Select the property type for these
properties.
(Use this option if you expect all the topics used in the calculation
to contain the input property - you can fill in the input property
values at any time. If the input property is not present on a topic,
that topic is ignored by the calculation)
Click OK.
Once you have created the formula, the topic will display a formula
icon 
.
You can see a list of the topic's formulas by pointing to this icon.
If you want to change the formula you created, you can edit
it in the Formula Editor.
Read Hint
Create
a formula with the Formula Editor
The Formula Editor lets you create sophisticated formulas that include
multiple functions (with grouping for precedence), input properties, and
ranges.
Select a topic on the map.
On the ribbon's Advanced
tab click Formulas.
The Formula Bar appears,
and shows all the formulas contained on the selected topic.
Click 
to open the Formula Editor
and enter the new formula.
On the Formula Editor Insert
bar, click 
to choose the topic property you want
to define by the calculation (this is the formula's left-hand side).
If you choose Other, enter the name for the new property between the
brackets that appear [ ].
Click to the right of the
= and type the formula. For speedier entry, you can use the Insert
bar buttons to choose functions 
, ranges 
, task attributes 
, or
select specific map topics 
to use in the calculation.
See Formula elements
and syntax in this topic for details.
Click 
to validate the formula. Errors will appear on the Formula Editor
status bar.
To cancel changes you made using the Formula Editor, click 
. You can close the Formula Editor by pressing
Esc.
Once you have created the formula, the topic will display a formula
icon .
Read Hint
Edit
or remove a formula
Select the topic that contains
the formula you want to change or delete.
If the Formula Bar
is not displayed, on the ribbon's Advanced
tab click Formulas, or click
the formula icon on the topic.
On the Formula Bar select
the formula you want to edit or remove. The Formula Editor starts.
To edit the formula, modify
the formula text manually or by using the Formula Editor buttons to
specify Topic Properties ,
Functions , Ranges , Task Attributes
, and to select topics .
To remove the formula, click 
.
You can close the Formula Editor by pressing
Esc.
Read Hint
Hide
formulas in all map topics
You
can hide formulas inserted in topics.
On the View
tab, click the Topic Info
drop-down.
Choose
Topic Info Display Settings.
Uncheck
the Formulas
check box.
All formulas in topics are hidden.
To show hidden formulas in topics, click the Show/Hide indicator 
in the bottom right
of the map, and choose Reset Show/Hide
to Default.
Include
hidden topics in formulas
By default, hidden topics are excluded from
calculations. However, you can modify map settings to include topics hidden
by filters.
Do one of the following:
· On the
File tab,
click Info,
and then click Map Settings.
· Right-click
the map’s workbook tab, and choose Map
Settings.
Disable the Recalculate
formulas when there are topics are hidden by filters check box.
Read Note
Formula
elements and syntax
General form
The general form for a formula is: lhs = rhs
lhs (left-hand side)
specifies the calculated topic property to be defined by this formula.
It will receive the results of the rhs (right-hand
side).
rhs (right-hand
side) is an expression that is evaluated to produce a numeric value.
This value is assigned to the calculated topic property specified by lhs.
This expression usually acts on a range of topics and / or on specific
topics.
Topic properties
In the Formula Editor, click
to insert a topic property in the equation. On the lhs,
you can select only properties in the current topic or create a new property.
On the rhs, you can select any property used
in the map.
Topic properties are always enclosed in square brackets [ ].
Examples: [cost] [unit_cost]
[all costs]
Topic properties used in formula calculations must
be of one of the following numeric types:
Operators and numbers
| + |
addition |
| - (hyphen) |
subtraction |
| * (asterisk) |
multiplication |
| / (forward slash) |
division |
| ^ |
exponentiation |
| ( ) (parentheses) |
grouping for precedence and enclose function arguments |
You can use numbers in equations and group operations with parentheses.
Example:
[Total] =
3*(5+2)+10*(2+2)
[Weighted_Profit]
= 3* [Profit]
- (2*([Cost]
/ 15))
Topic ranges or selection
Topic ranges are used in the formula's right-hand side to specify a
set of topics to use in the calculation. You can specify the range by
clicking in the Formula Editor, or by typing the
keywords below.
To refer to specific topics, click and then
select the topic in the map. If you do not specify a range for a property
the calculation uses that property from the current topic. This is displayed
in the formula as topic
. You can see the name of the map topic by pointing to this text in the
Formula Editor, or double-click it to navigate to the topic it references.
Read Note
Self |
this topic;
implicit, if range is not otherwise specified |
Parent |
parent of this topic;
evaluates to nothing for the Central Topic |
Branch |
this topic and all its descendants |
Children |
the immediate subtopics (1 level) of
this topic |
Descendants |
all subtopics (multiple levels) of this
topic |
Siblings |
all other topics that have the same
parent as this topic (not including this topic) |
Ancestors |
all topics (multiple levels) from which
this topic descends, not including this topic |
CentralTopic
|
the map's central topic |
MapTopics |
all topics in the map, including callouts
and floating topics |
Callouts |
callouts on this topic;
does not include callouts on boundaries and relationships |
Floating |
all floating topics in map, not including
their descendants |
Range Examples
[cost] - "The cost property of this topic."
(No range is specified, so "self"
is implied.)
Siblings [cost]
- "The cost property
of the siblings of this topic."
topic
[cost] - "The cost
property of the topic that was selected."
(Point to topic
to see the name of the topic referenced.)
Task attributes
Task attributes can be used in the formula's right-hand side. You can
insert a task attribute by clicking in the Formula
Editor, or by typing the keywords below.
EffortMinutes |
the amount of actual work time required
to complete a task measured in minutes
The effort for a specific resource can be specified by adding [R], where [R]is
the resource name. |
EffortHours |
the amount of actual work time required
to complete a task measured in hours.
The effort in hours for a specific resource can be specified
by adding [R], where
[R]is the resource
name. |
EffortDays |
the amount of actual work time required
to complete a task measured in days.
The effort in days for a specific resource can be specified by
adding [R], where
[R]is the resource
name. |
DurationMinutes |
the span of time during which the task
will be completed measured in minutes |
DurationHours |
the span of time during which the task
will be completed measured in hours |
DurationDays |
the span of time during which the task
will be completed measured in days |
Progress |
the amount of work completed on a task,
expressed as a percentage |
Priority
|
the priority level assigned to the task |
GeneralCost |
a fixed amount associated with the task |
ResourceCost[R] |
the resource cost for a resource where
[R] is the resource
name |
TotalResourceCost |
an amount associated with a task based
on the amount of effort of a resource and the resource's rate |
TotalCost |
all
costs associated with a task |
Function arguments are enclosed in parentheses.
Example: [Resource
Average cost] = Average (
MapTopics.TotalResourceCost)
Meaning: the "Resource Average
cost" topic property for this topic equals the Average
of the "TotalResourceCost"
task attribute of all topics in all map topics.
Read Note
Built-in functions
| SUM |
The sum of the property values. |
| AVERAGE |
The sum of the property values divided by the number of properties. |
| COUNT |
The number of properties. |
| MIN |
The smallest value of the property within the range. |
| MAX |
The largest value of the property within the range. |
Function arguments are enclosed in parentheses.
Example: [Total_cost]
= SUM ( Descendants
[cost] )
Multiple arguments are separated by , (commas).
Examples:
[Project_expense]
= SUM ([travel_expense],[equipment_expense],[salaries])
[Total_expense]
= SUM
(MapTopics[Project_expense] ,
topic[Operations_expense])
