Formulas - Aire Labs

Formulas in Aire Labs

Formulas are expressions that compute the value of a term 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

Financial

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

Math

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

Array

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

Conditional

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

Date

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`

String

Function	What it does
`LEFT(string, length)`	Left portion of a string
`RIGHT(string, length)`	Right portion of a string

Lookup & Navigation

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

Unit Conversion

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 for all supported unit strings.

Time & Calendar

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

Solver

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. It’s available throughout Aire — open it from the term detail panel, from any block view, or directly from a cell in 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 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.

Documentation Index

​Formulas in Aire Labs

​Writing Formulas

​Basic Syntax

​Operators

​Referencing Other Terms

​Constants

​Built-in Functions

​The Formula Editor

​Authoring

​Formula History