LET and LAMBDA, Reusable Logic

💡 Key concepts

• LET: =LET(name1, value1, name2, value2, calculation). Each value is calculated once.

  =LET(
     ev,  SUMIFS(EVM[BCWP], EVM[ProgramID], A2),
     ac,  SUMIFS(EVM[ACWP], EVM[ProgramID], A2),
     IF(ac=0, "", ev/ac)
  )
  

• LAMBDA: =LAMBDA(param1, param2, calculation). Test it in a cell by calling it immediately: =LAMBDA(x, x+1)(1). Then save it in Formulas → Name Manager with a clear name and comment.
• Useful program LAMBDAs:
  • CPI_SAFE = LAMBDA(ev, ac, IF(ac=0, NA(), ev/ac))
  • RAG = LAMBDA(idx, IFS(idx>=0.95,"Green", idx>=0.90,"Amber", TRUE,"Red")) (illustrative thresholds. Use your organization's.)
  • FY = LAMBDA(d, YEAR(d)+(MONTH(d)>=10))
  • WORKDAYS_SLIP = LAMBDA(base, fcst, NETWORKDAYS(base, fcst) - 1)
• Helpers:
  • SCAN(0, range, LAMBDA(acc, x, acc + x)) gives a running cumulative (current-period EVM → cumulative)
  • BYROW(array, LAMBDA(r, ...)) returns one result per row
  • MAP(arr1, arr2, LAMBDA(a, b, ...)) works element by element
  • REDUCE collapses to a single value
• Documentation habit: Put a comment in every Name Manager entry, since teammates need to know what RAG() does.

🛠 Hands-on exercise: EVM Function Library

Data: evm_monthly filtered to PRG-005 (PeriodEnd, BCWS, BCWP, ACWP, BAC).

1. Sort by PeriodEnd. Use SCAN to create cumulative BCWS, BCWP, and ACWP spills.
2. Create Name Manager LAMBDAs: CPI_SAFE, SPI_SAFE, EAC_CPI(bac, cumEV, cumAC) = bac / CPI_SAFE(cumEV, cumAC), TCPI(bac, cumEV, cumAC) = (bac - cumEV)/(bac - cumAC), RAG(idx).
3. Use MAP with your LAMBDAs to produce monthly CPI, SPI, and RAG spills.
4. Rewrite one long nested-IF formula from your own workbook (using fictionalized values) with LET.

📦 Mini-project: "EVM in One Cell"

Build one LET formula that takes a ProgramID (input cell) and returns a 1×6 horizontal array: cumulative BCWP, ACWP, CPI, SPI, EAC (BAC/CPI), VAC. Then wrap it as a LAMBDA called EVM_SNAPSHOT(programID) and use it with BYROW (or MAP) to fill a 12-program table. Document it in Name Manager.