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
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 |
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:#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
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
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
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 |
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
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
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
String
| Function | What it does |
|---|---|
LEFT(string, length) | Left portion of a string |
RIGHT(string, length) | Right portion of a string |
Unit Conversion
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 |
Time & Calendar
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 |
GOALSEEK(target, [...], tolerance?) | Iterative solver — finds the input that produces a target output |