LET
LET(name, value, …, result)
Names values so the result can use them, such as naming a task's slip once and then banding it. A name stands for its value and changes nothing else: [Total float] inside LET is still this row's float, and a set you name is read the same way, u[Critical]. Each name can be used in the values after it and in the result.
Arguments
| Argument | What to give it |
|---|---|
| name | A plain word, like u, slip or late. It can't be a name the language already uses (a relation such as Upstream, a function, TRUE, FALSE, AND, OR, NOT) or a name used earlier in the same formula. Names ignore case. |
| value | Anything: a number, a date, a set of rows, a what-if. |
| result | The formula's answer, using the names. |
Examples
Formula=LET(u, Upstream, COUNT(FILTER(u, u[Critical])))
How many critical tasks lead into this one, anywhere upstream.
Formula=LET(slip, WORKDAYS(IN(BASELINE(), [Finish date]), [Finish date]), IF(slip > 5d, "Late", IF(slip > 0d, "Slipping", "On plan")))
The slip against the baseline worked out once, then banded.
Measure (on the project)=LET(c, COUNT(FILTER(Tasks, Tasks[Critical])), ROUND(c / COUNT(Tasks) * 100, 0))
As a measure: the percentage of tasks that are critical.
Reach for something else when
| You want | Use |
|---|---|
| A value several formulas use | a formula column of its own, read by name like any field |
| A chain of conditions without nesting IFs | IFS |
Common questions
What does “rank is a function name (RANK); pick another name for this LET” mean?
The name is already a function, a relation (“Upstream is a relation name”) or a word of the language (AND, TRUE), whatever its capitals, so a LET can't reuse it. Pick another, such as my_rank or u. Names people reach for that are taken: rank, first, count, sum, today, all, tasks, project, calendar.
=LET(rank, RANK([Total float], All[Total float]), rank <= 10)The editor refuses this
=LET(Upstream, FILTER(Upstream, Upstream[Critical]), COUNT(Upstream))
What does “late is already named in this formula” mean?
Each name can be given once in a formula. A name can't be given a new value later on; give the new value its own name.
What does "LET takes name, value pairs and then a result" mean?
The last argument is the answer, and everything before it comes in pairs. LET(x, 1) has a name and a value but no result.
Can I write my own functions with LET?
No. A name holds a value, never a function: Owl leaves out Excel's LAMBDA, MAP and REDUCE. The lists from a named set line up with each other, so LET(u, Upstream, COUNT(FILTER(u, u[Phase] = [Phase]))) compares each upstream task's phase with this task's. See lists that line up.