Formula functions
All 39 functions you can use in a formula column. Formulas work the way
they do in any spreadsheet: start with =, refer to columns in square
brackets, like =[Revenue] - [Costs].
Math
ABS
=ABS(number)Returns a number with its sign removed, identical to Excel's ABS. The usual finance job: measuring the size of a variance without caring whether it is favorable or unfavorable.
Example
=ABS([Actual] - [Budget]) on a row with Actual 92,000 and Budget 100,000
returns 8000, the size of the variance whichever direction it ran. Pair with IF
to flag rows where the miss exceeds a threshold.
ROUND
=ROUND(number, num_digits)Rounds a number to a given count of decimal places, the way Excel's ROUND does: exact halves round away from zero, so 2.5 becomes 3 and -2.5 becomes -3.
Example
Sales tax at 8.25% on net revenue of 41,999:
=ROUND([Net_Revenue] * 0.0825, 2) turns 3,464.9175 into 3464.92, a
clean currency amount for the journal entry.
ROUNDDOWN
=ROUNDDOWN(number, num_digits)Rounds a number toward zero to a given count of decimal places: 9.99 becomes 9 at zero digits, and -9.99 becomes -9. Same rule as Excel's ROUNDDOWN. Use it where convention says fractions are dropped, not rounded.
Example
Accrued interest of 152.4791 is booked to the cent, dropping the fraction:
=ROUNDDOWN([Accrued_Interest], 2) returns 152.47.
ROUNDUP
=ROUNDUP(number, num_digits)Rounds a number away from zero to a given count of decimal places: 9.01 becomes 10 at zero digits, and -9.01 becomes -10. Same as Excel's ROUNDUP. Use it when partial units must be paid for in full.
Example
Product ships in cartons of 50. =ROUNDUP([Units_Sold] / 50, 0) on a row
with 487 units turns 9.74 into 10, the number of cartons you actually
have to order.
Aggregate
AVG
=AVG(number1, [number2], ...)Returns the average of its arguments (Excel's AVERAGE, shortened to AVG). Each bracketed reference supplies the current row's value, so AVG averages across columns within a row; for a column-wide average, use the GroupBy or Aggregate node. If any argument is blank, the result is blank.
Example
=AVG([Q1], [Q2], [Q3], [Q4]) on a row with 120,000 / 135,000 / 142,000 /
155,000 returns 138000, the average quarter for that line item.
COUNT
=COUNT(value1, [value2], ...)Counts how many of its arguments are filled in, skipping blanks. Useful for checking how complete a row is before you rely on totals built from it. For counting rows in a column, use the GroupBy or Aggregate node instead.
Example
Mid-quarter, only January and February actuals have landed.
=COUNT([Jan_Actual], [Feb_Actual], [Mar_Actual]) returns 2 on that row,
flagging that March is still missing.
MAX
=MAX(number1, [number2], ...)Returns the largest of its arguments. Each bracketed reference supplies the current row's value, so MAX compares across columns within a row; for the highest value in a whole column, use the GroupBy or Aggregate node. If any argument is blank, the result is blank.
Example
=MAX([Q1], [Q2], [Q3], [Q4]) on a row with 120,000 / 135,000 / 142,000 /
155,000 returns 155000, that line item's best quarter.
MIN
=MIN(number1, [number2], ...)Returns the smallest of its arguments. Each bracketed reference supplies the current row's value, so MIN compares across columns within a row; for the lowest value in a whole column, use the GroupBy or Aggregate node. If any argument is blank, the result is blank.
Example
Three vendors quoted the same job. =MIN([Bid_A], [Bid_B], [Bid_C]) on a row
with 41,200 / 39,750 / 44,010 returns 39750, the winning quote.
SUM
=SUM(number1, [number2], ...)Adds its arguments and returns the total. In a formula column each bracketed reference supplies the current row's value, so SUM totals across columns within a row; to total an entire column down the rows, use the GroupBy or Aggregate node. If any argument is blank, the result is blank.
Example
Your Budget table has quarterly columns. =SUM([Q1], [Q2], [Q3], [Q4]) on a
row with 120,000 / 135,000 / 142,000 / 155,000 returns 552000, that row's
full-year total.
Conditional
AVERAGEIF
=AVERAGEIF(criteria_column, criteria, average_column)Averages a whole column, counting only the rows where another column meets a
condition. Unlike Excel's AVERAGEIF, columns are given by position, counted
from zero: the leftmost column is 0, the next is 1. The criteria can be
text to match (case-insensitive) or a quoted comparison such as ">50000".
If no rows qualify, the result is blank.
Example
In a Sales table where Region is the first column (position 0) and Amount
is the second (position 1), with rows North 100,000 / South 200,000 /
North 150,000 / East 50,000: =AVERAGEIF(0, "North", 1) returns 125000,
the average North deal.
COUNTIF
=COUNTIF(criteria_column, criteria)Counts the rows where a column meets a condition. Unlike Excel's COUNTIF, the
column is given by position, counted from zero: the leftmost column is 0.
The criteria can be text to match (case-insensitive) or a quoted comparison
such as ">100000", using >, <, >=, <=, or <> for not-equal.
Example
In a Sales table where Amount is the second column (position 1), with
amounts 100,000 / 200,000 / 150,000 / 50,000: =COUNTIF(1, ">100000")
returns 2, the number of deals above 100,000.
SUMIF
=SUMIF(criteria_column, criteria, sum_column)Sums a whole column, counting only the rows where another column meets a
condition. Unlike Excel's SUMIF, columns are given by position, counted from
zero: the leftmost column is 0, the next is 1. The criteria can be text to
match (case-insensitive) or a quoted comparison such as ">50000", using
, <, >=, <=, or <> for not-equal.
Example
In a Sales table where Region is the first column (position 0) and Amount
is the second (position 1), with rows North 100,000 / South 200,000 /
North 150,000 / East 50,000: =SUMIF(0, "North", 1) returns 250000,
total North sales, repeated on every row.
Logical
AND
=AND(condition1, [condition2], ...)Returns TRUE only when every argument is true, same as Excel's AND. Most often nested inside IF to require several conditions at once.
Example
=IF(AND([Revenue] > 50000, [Margin] >= 0.2), "Priority", "Standard"): a
deal with Revenue 72,000 and Margin 0.24 passes both tests and returns
Priority. On its own, =AND([Revenue] > 50000, [Margin] >= 0.2) returns
TRUE for that row.
IF
=IF(condition, value_if_true, value_if_false)Returns one value when a condition is true and another when it is false. Behaves like Excel's IF, including short-circuit evaluation: only the branch that applies is calculated.
Example
On a row where Revenue is 2,400,000,
=IF([Revenue] > 1000000, "Enterprise", "Mid-market") returns
Enterprise. Conditions can compare
numbers or text with =, !=, >, <, >=, <=.
OR
=OR(condition1, [condition2], ...)Returns TRUE when at least one argument is true. Works the same way as in Excel. Combine with IF to act on rows that satisfy any of several conditions.
Example
=OR([Region] = "EMEA", [Region] = "APAC") returns TRUE on every EMEA or
APAC row; inside IF, it splits international business out of a transactions
table.
Lookup
HLOOKUP
=HLOOKUP(lookup_value, search_column, return_column, [row_offset])Finds an exact match in one column and returns the value from another column, optionally shifted up or down by a number of rows. This is not Excel's horizontal HLOOKUP. In Vireo it is a row-offset lookup, useful for reading the period after (or before) the one you matched. No match, or an offset that runs off the table, returns blank.
Example
Your Monthly table has Month and Revenue columns in date order.
=HLOOKUP("Mar", "Month", "Revenue", 1) finds the March row and returns the
Revenue one row below it (April's 118,000), putting next month's figure on
the current row.
INDEX
=INDEX(column_name, row_number)Returns the value at a given row of a named column. The row number counts from 1, and a row outside the table returns blank. Like Excel's INDEX, its natural partner is MATCH, which supplies the row number.
Example
=INDEX("Revenue", 3) returns the third row's Revenue: 142000 in a table
whose Revenue column starts 120,000 / 135,000 / 142,000. Combined with MATCH:
=INDEX("Balance", MATCH("Acme Corp", "Customer")) returns Acme's balance
wherever its row happens to sit.
MATCH
=MATCH(lookup_value, column_name, [match_type])Returns the row number (counting from 1) where a value appears in a named
column. Match type 0 (the default) requires an exact match; 1 finds the
largest value at or below the lookup; -1 finds the smallest value at or
above it. Not found returns blank. Pairs with INDEX to pull a value from the
same row of a different column.
Example
=MATCH("Acme Corp", "Customer") returns 7 when Acme sits on the seventh
row of the Customer column. =MATCH(50000, "Credit_Limit", 1) returns the row
of the highest limit that does not exceed 50,000.
VLOOKUP
=VLOOKUP(lookup_value, search_column, return_column, [range_lookup])Searches a column of the current table for a value and returns the entry from
another column on the matching row. Simpler than Excel's VLOOKUP: instead of a
range and a column number, you name the search and return columns as quoted
text. By default it uses approximate matching (the largest value less than or
equal to the lookup, for numbers); pass 0 as the last argument for an exact
match. No match returns blank.
Example
Your Rate_Card columns sit in the same table as your deals: Threshold
holds 0 / 100,000 / 500,000 and Discount holds 0 / 0.05 / 0.10.
=VLOOKUP([Order_Value], "Threshold", "Discount") on a 250,000 order returns
0.05: the tier whose threshold is the largest one at or below the order.
For exact ID lookups, use =VLOOKUP([Ticker], "Symbol", "Price", 0).
Text
CONCAT
=CONCAT(text1, [text2], ...)Joins its arguments into one text value. Behaves like Excel's CONCAT; numbers are converted to text automatically. If any argument is blank, the result is blank.
Example
=CONCAT([Region], "-", [Quarter]) on a row with Region EMEA and Quarter
Q3 returns EMEA-Q3, a composite key to group or join on.
FIND
=FIND(find_text, within_text, [start_pos])Returns the position (counting from 1) of one piece of text inside another. Case-sensitive, like Excel's FIND, but when the text is not found the result is blank instead of an error, which makes it safe inside IF. The optional start position skips earlier characters.
Example
Your Site_Code column holds values like US-EAST-042.
=FIND("-", [Site_Code]) returns 3, the position of the first dash. Feed
that into LEFT or MID to split the code apart.
LEFT
=LEFT(text, count)Returns the first characters of a text value. Works like Excel's LEFT, except the character count is required rather than defaulting to 1. Asking for more characters than the text has returns the whole text.
Example
Your Period column holds labels like 2026-Q3. =LEFT([Period], 4) returns
2026, the year portion, which makes a clean grouping key.
LEN
=LEN(text)Returns the number of characters in a text value, same behavior as Excel's LEN. Useful for validating codes and identifiers that must be a fixed width.
Example
Account codes should be eight characters, like 1000-410.
=IF(LEN([Account_Code]) = 8, "OK", "Check") returns OK for that value;
=LEN([Account_Code]) alone returns 8.
MID
=MID(text, start, length)Returns a slice from the middle of a text value, starting at a 1-based position. Identical to Excel's MID. If the slice runs past the end of the text, you get whatever is there.
Example
Your Site_Code column holds values like US-EAST-042. =MID([Site_Code], 4, 4)
starts at the fourth character and takes four, returning EAST.
RIGHT
=RIGHT(text, count)Returns the last characters of a text value. As in Excel, but the character count is required rather than defaulting to 1. If you ask for more characters than the text contains, you get the whole text.
Example
With Period labels like 2026-Q3, =RIGHT([Period], 2) returns Q3, the
quarter tag on its own.
SUBSTITUTE
=SUBSTITUTE(text, old_text, new_text)Replaces every occurrence of one piece of text with another. Like Excel's
SUBSTITUTE, but always replaces all occurrences; there is no
instance-number argument. Use an empty replacement "" to strip text out.
Example
Exported line items arrive as Revenue Actual, COGS Actual, and so on.
=SUBSTITUTE([Line_Item], " Actual", "") returns Revenue for the first
row: clean labels that match your chart of accounts.
Date
DATE_TRUNC
=DATE_TRUNC(date_serial, unit)Rolls a date back to the start of its period and returns that date's serial
number. The unit is quoted text: "year", "quarter", "month", "week"
(weeks start Monday), or "day". There is no Excel equivalent; it does in
one step what analysts usually improvise with MONTH/YEAR bucketing
formulas.
Example
=DATE_TRUNC(TODAY(), "quarter") on July 27, 2026 returns 46204, the
serial for July 1, 2026, the start of Q3. Every date in the same quarter
returns the same value, which makes it a clean grouping key for
period-over-period summaries.
DAY
=DAY(date_serial)Returns the day of the month (1 through 31) of a date serial number, such as one produced by TODAY, EDATE, MONTH_END, or DATE_TRUNC. Same as Excel's DAY. An invalid serial returns blank.
Example
=DAY(MONTH_END(TODAY())) returns 31 in July 2026, the number of days in
the current month. Handy for daily run-rate calculations like
[MTD_Revenue] / DAY(TODAY()).
EDATE
=EDATE(date_serial, months)Returns the date a given number of months before or after a date, as a serial number. Follows Excel's EDATE rule: the day of month is preserved, clamping to the month's last day when the target month is shorter (January 31 plus one month lands on February 28 or 29).
Example
=EDATE(TODAY(), 3) on July 27, 2026 returns the serial for October 27,
2026, a 90-day-terms maturity date. Negative months walk backward:
=EDATE(TODAY(), -12) is the same date last year, the basis for a
year-over-year comparison.
MONTH
=MONTH(date_serial)Returns the month (1 through 12) of a date serial number, such as one produced by TODAY, EDATE, MONTH_END, or DATE_TRUNC. Behaves the same as in Excel. An invalid serial returns blank.
Example
=MONTH(TODAY()) returns 7 on July 27, 2026. Useful for tagging the
current reporting month: =IF(MONTH(TODAY()) >= 10, "Q4", "Q1-Q3").
MONTH_END
=MONTH_END(date_serial, [months])Returns the last day of a date's month as a serial number, optionally shifted by a number of months first. This is Excel's EOMONTH under a plainer name. Leap years are handled correctly, and a negative offset walks backward.
Example
=MONTH_END(TODAY()) on July 27, 2026 returns the serial for July 31, 2026,
the current month's close. =MONTH_END(TODAY(), 1) returns August 31, useful
for setting invoice due dates at the end of next month.
TODAY
=TODAY()Returns today's date as a serial number, using the same day-count Excel uses, so July 27, 2026 is 46230. On its own the serial is a bare number; the other date functions read it, so TODAY usually appears nested inside them.
Example
=YEAR(TODAY()) returns 2026 on July 27, 2026, and
=MONTH_END(TODAY()) returns the serial for July 31, the current
reporting month's close date.
WEEKNUM
=WEEKNUM(date_serial)Returns the ISO week number (1 through 53) of a date serial number, with weeks starting on Monday. This is the international standard week: the behavior of Excel's ISOWEEKNUM rather than Excel's default WEEKNUM. An invalid serial returns blank.
Example
=WEEKNUM(TODAY()) returns 31 on July 27, 2026, the tag a weekly sales
or cash report would carry for that date.
YEAR
=YEAR(date_serial)Returns the year of a date serial number, such as one produced by TODAY, EDATE, MONTH_END, or DATE_TRUNC. Equivalent to Excel's YEAR. An invalid serial returns blank.
Example
=YEAR(TODAY()) returns 2026 on July 27, 2026. Combine with EDATE to
label a forward period: =YEAR(EDATE(TODAY(), 6)) returns 2027, the year
six months out.
Financial
FV
=FV(rate, nper, pmt, [pv], [type])The future value of an investment, given a constant rate per period, the
number of periods, and a regular payment. Uses the same sign convention as
Excel's FV: money you pay in is negative, so the balance you get back comes
out positive. Optional pv is a starting balance (default 0); type is 0
for payments at period end (default) or 1 for period start.
Example
=FV(0.06/12, 120, -500): saving $500 a month for 10 years at 6% annual
interest grows to about 81939.67. The payment is negative because it is
cash you put in each month.
IRR
=IRR(cashflow1, cashflow2, [cashflow3], ...)The internal rate of return of a series of cash flows: the discount rate at which their net present value is zero. Follows Excel's convention: the first cash flow sits at time zero, so the initial investment goes in as a negative first argument. The series needs at least one sign change; if the search does not converge, the result is blank.
Example
Invest $10,000 and get back 3,000 / 4,200 / 6,800 over three years:
=IRR(-10000, 3000, 4200, 6800) returns about 0.1634, a 16.3% internal
rate of return.
Compare it against your cost of capital before approving the project.
NPV
=NPV(rate, cashflow1, [cashflow2], ...)Discounts a series of cash flows at a constant rate and returns their total present value. Uses Excel's NPV convention: the first cash flow is discounted one full period, so an upfront investment at time zero should be added outside the function, not passed as the first argument.
Example
A project costs $10,000 today and returns 3,000 / 4,200 / 6,800 at the end of
years one through three. At a 10% discount rate,
=NPV(0.1, 3000, 4200, 6800) - 10000 returns about 1308.04. The project
adds value after covering its cost of capital.
PMT
=PMT(rate, nper, pv, [fv], [type])The periodic payment on a loan, given a constant rate per period, the number
of payments, and the amount borrowed. Follows Excel's PMT, sign convention
included: a positive loan amount produces a negative payment, because it is
money you pay out. Optional fv is a balance to end with (default 0);
type is 0 for payments at period end (default) or 1 for period start.
Example
=PMT(0.05/12, 360, 400000) is the monthly payment on a $400,000 30-year
mortgage at 5% annual interest: about -2147.29. Note the rate is per
period, so annual 5% becomes 0.05/12 for monthly payments.
PV
=PV(rate, nper, pmt, [fv], [type])The present value of a stream of equal payments: what that stream is worth
today at a given rate per period. Works like Excel's PV, with the same sign
convention: payments you receive are entered as negative pmt from the
payer's view, so the result carries the opposite sign of the payment.
Optional fv is a final balance (default 0); type is 0 for payments at
period end (default) or 1 for period start.
Example
=PV(0.08/12, 240, -1500): a $1,500 monthly payment for 20 years,
discounted at 8% annual, is worth about 179331 today. This is the standard
way to size a loan a borrower can support at that payment.