> ## Documentation Index > Fetch the complete documentation index at: https://docs.airelabs.com/llms.txt > Use this file to discover all available pages before exploring further. # Formulas > Formulas compute term values from other terms, constants, and built-in functions. ## Formulas in Aire Labs Formulas are expressions that compute the value of a [term](/docs/terms) from other terms, constants, and built-in functions. They support standard arithmetic, arrays, units, financial calculations, and time-series logic. *** ## Writing Formulas ### Basic Syntax ``` Revenue * CapacityFactor (CapEx + OpEx) / 12 IF(UtilizationRate > 0.8, HighTariff, LowTariff) ``` ### Operators | Operator | Meaning | Example | | ----------------- | ---------- | -------------------- | | `+` | Add | `Revenue + Grant` | | `-` | Subtract | `Revenue - Costs` | | `*` | Multiply | `Price * Volume` | | `/` | Divide | `CapEx / Lifetime` | | `^` | Exponent | `(1 + Rate) ^ Years` | | `==` | Equal | `Scenario == 1` | | `!=` | Not equal | `Status != 0` | | `>` `<` `>=` `<=` | Comparison | `IRR >= HurdleRate` | | `&&` | And | `A > 0 && B > 0` | | `\|\|` | Or | `A == 1 \|\| B == 1` | Comparison and logical operators return `1` (true) or `0` (false). ### Referencing Other Terms Refer to other terms by their name. Aire Labs resolves references from right to left — you can use a term's full name or any unique suffix: ``` Project_Operations_Revenue ``` ``` Operations_Revenue ``` ``` Revenue ``` All three resolve to the same term as long as the suffix is unambiguous. If a suffix matches more than one term, the formula will show an `#ambiguous` error. Use a longer suffix to disambiguate. ### Constants | Constant | Value | | -------- | ---------------- | | `pi` | 3.14159… | | `true` | 1 | | `false` | 0 | | `null` | empty / no value | *** ## Built-in Functions | Function | What it does | | ------------------------------------------- | ------------------------------------------------------------------------------------ | | `NPV(rate, values)` | Net present value of a cash flow series discounted at `rate` | | `IRR(values)` | Internal rate of return — first value is typically the initial investment (negative) | | `XIRR(values, dates)` | IRR for cash flows with irregular dates | | `XNPV(rate, values, dates)` | NPV for cash flows with irregular dates | | `PV(rate, nper, pmt, fv?, when?)` | Present value of an annuity | | `FV(rate, nper, pmt, pv?, when?)` | Future value of an annuity | | `PMT(rate, nper, pv, fv?, when?)` | Periodic payment for a loan — `rate` is per period, `pv` is negative | | `IPMT(rate, per, nper, pv, fv?, when?)` | Interest portion of a loan payment for a specific period | | `PPMT(rate, per, nper, pv, fv?, when?)` | Principal portion of a loan payment for a specific period | | `MIRR(values, finance_rate, reinvest_rate)` | Modified internal rate of return | | `PAYBACK(values, initial_investment)` | Number of periods to recover the initial investment | | Function | What it does | | ------------------------- | ----------------------------------------------- | | `ABS(x)` | Absolute value | | `SQRT(x)` | Square root | | `POW(base, exponent)` | Raises base to a power | | `EXP(x)` | e raised to x | | `LN(x)` | Natural logarithm | | `LOG(x, base?)` | Logarithm (default base e) | | `LOG10(x)` | Base-10 logarithm | | `ROUND(x, decimals?)` | Round to nearest — works element-wise on arrays | | `ROUNDUP(x, decimals?)` | Always round away from zero | | `ROUNDDOWN(x, decimals?)` | Always round toward zero | | `CEIL(x)` | Round up to nearest integer | | `FLOOR(x)` | Round down to nearest integer | | `MOD(x, y)` | Remainder after division | | Function | What it does | | ------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------- | | `SUM(array)` | Total of all elements | | `AVERAGE(array)` | Mean | | `MAX(array)` | Largest value | | `MIN(array)` | Smallest value | | `PRODUCT(array)` | Product of all elements | | `COUNT(array)` | Number of non-null elements | | `SUMPRODUCT(array1, array2, ...)` | Sum of element-wise products | | `CUMSUM(array)` | Cumulative sum — e.g. `[100, 200, 300]` → `[100, 300, 600]` | | `CUMPROD(array)` | Cumulative product | | `CUMMAX(array)` | Running maximum across an array — e.g. track peak generation output over a project's operating period | | `CUMMIN(array)` | Running minimum across an array — e.g. track floor prices over time | | `ARGSORT(array)` | Returns the sort-order indices of an array — e.g. rank revenue streams by size without reordering the underlying model structure | | `RANGE(start, end, step?)` | Generate a sequence — e.g. `RANGE(1, 5)` → `[1, 2, 3, 4, 5]` | | `DISTRIBUTE(total, periods)` | Spread a value evenly across periods — useful for straight-line CapEx | | `REPEAT(value, count)` | Repeat a value n times | | `LEN(array)` | Number of elements | | `SLICE(array, start, end)` | Extract a portion (0-indexed, end exclusive) | | `CONCAT(array1, array2)` | Join two arrays | | `PUSH(array, value)` | Append a value to an array | | `GET(array, index)` / `INDEX(array, index)` | Element at position — negative index counts from end | | `INDEXOF(array, value)` | Find position of a value | | `PAD(array, length)` | Extend array to target length with nulls | | `UNIQUE(array)` | Remove duplicates | | `MASK(array, condition)` | Filter by condition — returns only matching elements | | `ISIN(value, array)` | Returns `1` if value is in array, `0` if not | | `MATCH(value, array)` | Find position of value — returns `null` if not found | | `XLOOKUP(lookup, lookup_array, return_array, if_not_found?)` | Look up a value and return the corresponding element from another array | | Function | What it does | | ---------------------------------------- | ---------------------------------------------- | | `IF(condition, true_value, false_value)` | Returns one of two values based on a condition | | `SUMIF(array, condition)` | Sum of elements where condition is true | | `COUNTIF(array, condition)` | Count of elements where condition is true | | Function | What it does | | --------------------------- | ----------------------------------------------------- | | `EDATE(date, months)` | Add months to a date | | `EOMONTH(date, months)` | Last day of the month, offset by months | | `DATEDIF(start, end, unit)` | Difference between dates — units: `"Y"`, `"M"`, `"D"` | | `YEAR(date)` | Extract the year | | `MONTH(date)` | Extract the month | | `DAY(date)` | Extract the day | | `BEFORE(date1, date2)` | Returns `1` if date1 is before date2 | | `AFTER(date1, date2)` | Returns `1` if date1 is after date2 | | `BETWEEN(date, start, end)` | Returns `1` if date falls within range | | `CMP(date1, date2)` | Returns `-1`, `0`, or `1` | | Function | What it does | | ----------------------- | ------------------------- | | `LEFT(string, length)` | Left portion of a string | | `RIGHT(string, length)` | Right portion of a string | These functions use lowercase names — this is intentional and reflects the underlying API convention. | Function | What it does | | --------------------------- | ------------------------------------------------------------------------------------------------ | | `findheader(array, value)` | Locates a matching header within your model structure — useful for navigating labeled data | | `findancestor(term, block)` | Traverses the block hierarchy to find an ancestor term in a parent block | | `assoc(key, pairs)` | Associative lookup — returns the value paired with a matching key from a list of key-value pairs | | Function | What it does | | ------------------------------------ | ------------------------------------------------------------------------------------ | | `CONVERT(value, from_unit, to_unit)` | Converts a value between compatible units — e.g. `CONVERT(1, "MWh", "kWh")` → `1000` | See [Units](/docs/units) for all supported unit strings. | Function | What it does | | -------------------------------- | ---------------------------------------------------------------------------- | | `LAG(array, offset?, initial?)` | Returns previous-period values — essential for period-to-period dependencies | | `PERIODS(duration, period_unit)` | Converts a physical duration to a dimensionless period count | | `CALENDAR(rate, period)` | Converts a physical rate to a calendar rate | | Function | What it does | | ------------------------------------- | ---------------------------------------------------------------- | | `GOALSEEK(target, [...], tolerance?)` | Iterative solver — finds the input that produces a target output | *** ## The Formula Editor The formula editor is where you write and edit formulas for any calculated [term](/docs/terms). It's available throughout Aire — open it from the term detail panel, from any block view, or directly from a cell in [All Blocks](/docs/all-blocks). Grid Mode adds one particularly useful entry point: clicking a calculated term opens the editor inline without navigating away from your model, so you can edit formulas while keeping your place in the spreadsheet. See [All Blocks](/docs/all-blocks) for the full layout options. ### Authoring * **Enter to insert** — Pressing Enter on any value or formula line inserts a new line directly below. * **Line numbers** — Line numbers are visible in the editor for easier navigation in longer formulas. * **Inline computed values** — Computed values display inline next to the formula that produced them. * **Human-readable display names** — Formulas show display names for referenced terms; hover to reveal the underlying identifier. ### Formula History Every formula has a full version history accessible inside the editor. Select the **History** tab to see exactly how the expression has changed over time, who made each change, and when.