Reading other rows
A formula reaches other rows through relations: names like Predecessors, Upstream and Tasks that mean "these rows, seen from this one". Predecessors[Finish date] is the finish date of each of this task's predecessors. A relation follows the schedule's logic, the WBS, or the resource assignments, never the order of the rows in the grid, so the answer is the same however the grid is sorted.
A relation gives several rows, so reading a field through it gives several values: a list. A formula's result must be one value per row, so a list is always reduced, by a function that turns many values into one: MAX(Predecessors[Finish date]), COUNT(Upstream), ANY(Successors[Critical]).
=Predecessors[Finish date]
Refused: “Predecessors[Finish date] is a list (one Date per predecessor). Reduce it with MAX, MIN, …”
Relations
From a task:
| Relation | The rows |
|---|---|
Predecessors, Successors | The tasks linked directly before or after this one |
Upstream, Downstream | Every task that leads into this one, or that it leads into, at any depth |
DrivingPredecessors, DrivingSuccessors | Only over links that drive (that set the successor's early start) |
DrivingUpstream, DrivingDownstream | The driving chain, at any depth |
InLinks, OutLinks | The links into or out of this task. Each carries the link's own fields ([Link type], [Lag], [Link slack], [Driving link]) and the fields of the task at the far end |
PredecessorLinks, SuccessorLinks | The same links as rows of the relationships table: [Type], [Lag], [Slack], [Driving], and Predecessor[…] or Successor[…] |
Assignments | The resources assigned to this task, with their units and the resource's fields |
Expenses | This task's expenses: costs that aren't a resource's units, such as a permit or a hire |
WBSNode | This task's WBS node: one row, WBSNode[Path] |
Calendar | The calendar the task is dated on: one row, Calendar[Hours per day], Calendar[Short name] (its id) |
All | Every task in the schedule |
From any row: Project (the project's one row, Project[Finish date]), Relationships (every relationship), ResourceAssignments (every assignment), Expenses (every expense) and Calendars (every calendar of the project).
=COUNT(FILTER(InLinks, InLinks[Lag] < 0h))
How many of this task's links into it carry a lead.
Formula=WORKDAYS([Finish date], Project[Finish date])
How far this task finishes before the project does.
The relations of the other tables are under tables.
Sets and lists
A relation is a set of rows. A field read through it is a list, one value per row, in the same order. Functions that take sets and lists:
| You want | Use |
|---|---|
| How many rows | COUNT(Upstream) |
| A total, the largest, the mean | SUM, MAX, AVERAGE of a list |
| Whether any or every row passes | ANY, EVERY |
| Some of the rows | FILTER |
| The row with the largest value, not the value | ARGMAX(Upstream, Upstream[Finish date])[Task code] |
| Exactly one row, or an error | ONLY |
| Whether there are none | ISEMPTY |
| Rows in both, either, or one but not the other | INTERSECT, UNION, EXCEPT |
A set with one row in it is still a set: MAX(Predecessors[Finish date]) is right with one predecessor or ten. A one-row relation (WBSNode, Calendar, Project, an assignment's Task) is a single row, read directly: WBSNode[Name].
Sets have a fixed order, the order CPM dates the tasks in, then task code, so FIRST, TEXTJOIN and ties in ARGMAX always give the same answer.
Filtering
FILTER keeps the rows of a set where a condition is TRUE. The condition is about the set you're filtering, read through the same name:
Formula=COUNT(FILTER(Predecessors, Predecessors[Status] <> "Complete"))
How many predecessors aren't complete.
Inside the condition, a bare field still means this row. That is how a filter compares other rows with the one you're on:
Formula=COUNT(FILTER(Predecessors, Predecessors[Phase] <> [Phase]))
How many predecessors are in a different phase from this task.
A filter that keeps nothing is an empty set, not an error: COUNT of it is 0, SUM 0, MAX blank.
Excel's SUMIF and COUNTIF are a filter and then the sum: SUM(FILTER(All, All[Phase] = "Civil")[Duration]).
Lists that line up
Arithmetic and comparisons work element by element on lists from the same set, and repeat a single value across a list:
Formula=SUM(Assignments[Budgeted units] * Assignments[Hourly rate])
Each assignment's hours times its rate, then added up: the task's budget.
Two lists from different sets don't line up, because nothing says which element goes with which:
The editor refuses this=MAX(Predecessors[Finish date] - Successors[Start date])
Refused: the two lists come from different sets. Name the set once with LET to read two fields of it: LET(u, Upstream, FILTER(u, u[Phase] = [Phase])).
One task by its ID
Task("A1000") is one task, by its ID: Task("A1000")[Finish date]. The editor checks the ID when you save and stores the task itself, so renaming its code later doesn't break the formula.
SAME([Phase]) is every task with the same Phase as this one, this one included: SUM(SAME([Phase])[Duration]) is the total duration of the task's phase.
Columns that read themselves
A formula column can read its own value on the rows next to it in the logic, the way CPM's forward pass reads each predecessor's finish. Rows are worked out in logic order, so a predecessor's value is ready before its successors need it:
Formula column DepthDepth = IF(ISEMPTY(Predecessors), 0, MAX(Predecessors[Depth]) + 1)
How many links lie between this task and the start of the schedule.
The rules, each checked when you save:
| A column may read itself through | Not through |
|---|---|
One step back: Predecessors, InLinks, DrivingPredecessors, or a FILTER of them | Upstream or Downstream: a row's own closure leads back to itself |
Or one step forward: Successors, OutLinks, DrivingSuccessors | Both directions in one column |
On WBS rows: Parent, or Children | A bare [Depth] on its own row |
A task on a loop in the logic gets the error "on a schedule loop"; every other task still works out.
A self-reading column is arithmetic over the dates CPM already worked out. It's not CPM: a delay added up along a path ignores calendars, constraints and progress. For what CPM would do, ask CPM with a what-if.
Tables
Every formula belongs to one table and is worked out once for each of its rows. A field is the row's own; a relation's name means what it means on that table, so Tasks on a WBS node is every task under it, and on a resource every task it is assigned to.
| Table | Where you write it | Its fields | Its relations |
|---|---|---|---|
| Task | A formula column; most guardrails | The task fields | Above |
| WBS node | A column's formula on WBS rows; a guardrail on each WBS node | [Name], [Path], [Code], [Level] (1 at the top), [Start date], [Finish date] | Parent, Children, Ancestors, Descendants, Tasks (every task in or under it), All |
| Project | A measure; a guardrail on the project | [Name], [Data date], [Start date], [Finish date], and every measure | Tasks, WBS, Resources, Assignments, Expenses |
| Resource | A resource formula; a guardrail on each resource | [Name], [Resource ID], [Resource type], [Hourly rate], [Currency], [Max units per time] (the most of it at once, 1 = one full-time resource when nobody set one) | Assignments, Tasks, All |
| Assignment | A guardrail on each assignment | [Resource], [Resource ID], [Resource type] (labor, equipment or material), [Hourly rate], [Currency], [Actual units], [Remaining units], [Rate] (the default columns: what was entered, else worked out, see cost columns), [Actual units (entered)], [Remaining units (entered)], [Rate (entered)] (exactly what was entered, blank when nothing was), [Budgeted units (entered)], [Units per time (entered)] (what was entered: one of them may be blank), [Budgeted units], [Units per time] (the default columns: the budget, else units per time × the task's working hours; units per time, else budget ÷ hours), [Curve] | Task (one task: Task[Start date]), All |
| Expense | A guardrail on each expense | [Expense ID], [Name], [Cost account], [Budget cost], [Actual cost], [Remaining cost] (the default columns: as entered, else 0 and budget less actual), [Actual cost (entered)], [Remaining cost (entered)] (exactly what was entered), [Accrual] (start, end or uniform) | Task (one task: Task[Finish date]), All |
| Relationship | A guardrail on each relationship | [Type] (FS, SS, FF, SF), [Lag], [Slack], [Driving], [Predecessor], [Successor] | Predecessor, Successor (one task each), All |
| Calendar | A guardrail on each calendar; a task reads its own as Calendar[…], and any task's off its [Calendar]: Successor[Calendar][Hours per day], Tasks[Calendar][Short name] | [Name], [Short name], [Hours per day] (what 1d counts on it: a night shift is one day), [Hours per week], [Workdays per week] (days of its week a shift starts on), [Holidays], [Exceptions] | Tasks, Resources (those on it), All |
=SUM(Tasks[Duration])
On a WBS row: the duration of every task in the node and below it.
Measure (on the project)=COUNT(FILTER(Tasks, Tasks[Critical]))
A measure: how many tasks are critical.
Formula on relationships=IF([Successor][Status] <> "Complete", [Lag] <= 0h)
A guardrail on each relationship: no lag into work that isn't complete. Relationships into completed work are blank, so they aren't checked.
Formula=Calendar[Hours per day] <= 8 AND Calendar[Hours per week] <= 40
On a task: TRUE when its calendar's days are 8 hours or less, and its weeks 40.
Formula on calendars=[Workdays per week] <= 6 AND COUNT(Tasks) > 0
A guardrail on each calendar: none works all seven days, and every one has a task on it. Every calendar of the project is a row, one no task is on included.
[Calendar] is the calendar's name, and a field read off it is the calendar's, so any task's calendar is one bracket away: a relationship's ends, a link's far task, every task of a set. Compare calendars by [Short name], their id: two calendars can share a name, and a renamed one keeps its id.
=Predecessor[Calendar][Workdays per week] = Successor[Calendar][Workdays per week]
A guardrail on each relationship: both ends work the same week.
Formula on WBS rows=COUNTDISTINCT(Tasks[Calendar][Short name])
On a WBS row: how many calendars its tasks are on.
Measure (on the project)=EVERY(FILTER(Tasks, Tasks[Type] = "Milestone")[Calendar][Workdays per week] = 7)
A measure: TRUE when every milestone is on a seven-day calendar.
Measure (on the project)=COUNT(Calendars)
A measure: how many calendars the project has.
Your own data tables (an imported cost ledger, a delivery log) are tables too, read from any row by their formula name: SUM(FILTER(CostLedger, CostLedger[Code] = [Task code])[Amount]) adds up the ledger lines of this task. See Tables and your own data.
Columns can read each other across tables: a task column can divide by a measure, [Duration] / Project[Total duration], and a WBS formula can add up a task column, SUM(Tasks[Near critical days]). A chain that leads back to where it started is refused: “Room reads Chain to here, which reads Room.”
Common questions
Is there a VLOOKUP?
No: relations and Task("…") replace it. To read a value off a related task, name the relation (Predecessors[Finish date], WBSNode[Name], Task("A1000")[Finish date]). To find a task by some other field, filter and take the one row: ONLY(FILTER(All, All[Name] = "Pour slab"))[Finish date].
Why is Upstream[Total float] slow when Predecessors[Total float] isn't?
Upstream can hold thousands of tasks, for each of thousands of rows. The editor shows how long a formula will take on your schedule before you save it, and refuses one that would take too long, naming the expensive part. Comparing against a fixed value, or grouping with SAME(…), keeps it fast.
How do I get the successors of each predecessor?
A list of sets isn't something a formula can hold. Write a helper column on the predecessors' side (say, COUNT(Successors)), then read it through the relation: MAX(Predecessors[Successor count]).