Function reference by category | Altruistiq Help Center

Functions perform specific calculations and operations on your data. Use this reference to find the right function for your task—whether you're calculating totals, working with dates, or manipulating text.

You don't need to memorise function names. As you type in the formula bar, suggestions appear automatically to help you discover available functions and complete your formulas.

Aggregate functions

Aggregate functions calculate a single result from multiple rows of data. Use these when you need totals, averages, counts, or other calculations across groups of records.

Function	Description	Example
ArrayAgg	Collects values into a list (array)	`ArrayAgg([Product Code])`
ArrayAggDistinct	Collects unique values into a list (array)	`ArrayAggDistinct([Category])`
Avg	Calculates the average of values	`Avg([Energy Consumption])`
AvgIf	Calculates average for values meeting a condition	`AvgIf([Category] = "Transport", [CO2e])`
Corr	Calculates the correlation between two columns	`Corr([Temperature], [Energy Use])`
Count	Counts the number of non-empty values	`Count([Transaction ID])`
CountDistinct	Counts unique values only	`CountDistinct([Supplier ID])`
CountDistinctIf	Counts unique values meeting a condition	`CountDistinctIf([Status] = "Active", [Customer ID])`
CountIf	Counts values that meet a condition	`CountIf([Status] = "Complete", [ID])`
GrandTotal	Calculates the grand total for a column	`GrandTotal([Emissions])`
ListAgg	Joins values into a text string with a separator	`ListAgg([Tag], ", ")`
ListAggDistinct	Joins unique values into a text string	`ListAggDistinct([Category], "; ")`
Max	Finds the largest value	`Max([Carbon Intensity])`
MaxIf	Finds the largest value meeting a condition	`MaxIf([Year] = 2024, [Emissions])`
Median	Finds the middle value	`Median([Response Time])`
Min	Finds the smallest value	`Min([Efficiency Rating])`
MinIf	Finds the smallest value meeting a condition	`MinIf([Type] = "Renewable", [Cost])`
PercentileCont	Calculates a continuous percentile value	`PercentileCont([Score], 0.9)`
PercentileDisc	Calculates a discrete percentile value	`PercentileDisc([Rating], 0.5)`
PercentOfTotal	Calculates what percentage a value contributes to the total	`PercentOfTotal([Emissions])`
RegressionIntercept	Calculates the y-intercept of a regression line	`RegressionIntercept([Y], [X])`
RegressionR2	Calculates the R² value of a regression line	`RegressionR2([Actual], [Predicted])`
RegressionSlope	Calculates the slope of a regression line	`RegressionSlope([Y], [X])`
StdDev	Calculates the standard deviation	`StdDev([Monthly Emissions])`
Subtotal	Calculates the subtotal for a group	`Subtotal([Revenue])`
Sum	Adds all values in a column or group	`Sum([Emissions])`
SumIf	Adds values that meet a condition	`SumIf([Region] = "UK", [Emissions])`
SumProduct	Multiplies corresponding values and sums the results	`SumProduct([Quantity], [Unit Price])`
Variance	Calculates the sample variance	`Variance([Daily Output])`
VariancePop	Calculates the population variance	`VariancePop([Measurements])`

Array functions

Array functions work with lists of values. These are useful when you need to store multiple values in a single cell or work with data that has a list structure.

Function	Description	Example
Array	Creates a list from specified values	`Array("Red", "Green", "Blue")`
ArrayCompact	Removes empty values from a list	`ArrayCompact([Tags])`
ArrayConcat	Combines multiple lists into one	`ArrayConcat([List1], [List2])`
ArrayContains	Checks if a list contains a specific value	`ArrayContains([Categories], "Scope 1")`
ArrayDistinct	Removes duplicate values from a list	`ArrayDistinct([Items])`
ArrayExcept	Returns values in the first list not found in the second	`ArrayExcept([All Items], [Excluded])`
ArrayIntersection	Returns values that appear in both lists	`ArrayIntersection([List1], [List2])`
ArrayJoin	Joins list items into a text string	`ArrayJoin([Tags], ", ")`
ArrayLength	Counts the number of items in a list	`ArrayLength([Products])`
ArraySlice	Extracts a portion of a list	`ArraySlice([Items], 1, 3)`
RaggedHierarchy	Creates a hierarchy from multiple columns	`RaggedHierarchy([Level1], [Level2], [Level3])`
Sequence	Creates a list of sequential numbers	`Sequence(1, 12)`
SplitToArray	Splits text into a list using a delimiter	`SplitToArray([Tags], ",")`
Sparkline	Generates a sparkline chart from data	`Sparkline([Monthly Values])`
SparklineAgg	Generates a sparkline by aggregating values over time	`SparklineAgg([Date], [Value])`

Date functions

Date functions let you work with dates and times—calculating differences, extracting components, or shifting dates forward and back.

Function	Description	Example
ConvertTimezone	Converts a date to a different time zone	`ConvertTimezone([Timestamp], "UTC", "Europe/London")`
DateAdd	Adds a time period to a date	`DateAdd("month", 1, [Report Date])`
DateDiff	Calculates the difference between two dates	`DateDiff("day", [Start Date], [End Date])`
DateFormat	Formats a date as text	`DateFormat([Date], "DD/MM/YYYY")`
DateFromUnix	Converts a Unix timestamp to a date	`DateFromUnix([Timestamp])`
DateLookback	Returns a value from a previous time period	`DateLookback([Emissions], [Date], -1, "year")`
DatePart	Extracts a specific component from a date	`DatePart("quarter", [Date])`
DateParse	Converts text to a date value	`DateParse([Date Text], "YYYY-MM-DD")`
DateTrunc	Truncates a date to a specific period	`DateTrunc("month", [Transaction Date])`
Day	Extracts the day of the month from a date	`Day([Delivery Date])`
DayOfYear	Returns the day number within the year (1–366)	`DayOfYear([Date])`
EndOfMonth	Returns the last day of the month	`EndOfMonth([Date])`
Hour	Extracts the hour from a date/time	`Hour([Timestamp])`
InDateRange	Checks if a date falls within a range	`InDateRange([Date], "last 30 days")`
InPriorDateRange	Checks if a date falls within a prior period	`InPriorDateRange([Date], "last quarter")`
LastDay	Returns the last day of a specified period	`LastDay([Date], "quarter")`
MakeDate	Creates a date from year, month, and day values	`MakeDate([Year], [Month], [Day])`
Minute	Extracts the minute from a date/time	`Minute([Timestamp])`
Month	Extracts the month number from a date	`Month([Invoice Date])`
MonthName	Returns the month name from a date	`MonthName([Date])`
Now	Returns the current date and time	`Now()`
Quarter	Extracts the quarter number from a date	`Quarter([Fiscal Date])`
Second	Extracts the second from a date/time	`Second([Timestamp])`
Today	Returns the current date	`Today()`
Weekday	Returns the day of the week as a number (1–7)	`Weekday([Date])`
WeekdayName	Returns the day name from a date	`WeekdayName([Date])`
Year	Extracts the year from a date	`Year([Reporting Period])`

Join functions

Join functions retrieve data from other tables based on matching values. These work similarly to VLOOKUP in Excel.

Function	Description	Example
Lookup	Finds a matching value and returns a corresponding value from another column	`Lookup([Supplier ID], [Suppliers/ID], [Suppliers/Name])`
Rollup	Finds matching values and calculates a total from another column—similar to SUMIF in Excel, but pulls data from a different table	`Rollup([Category], [Products/Category], Sum([Products/Emissions]))`

Logical functions

Logical functions evaluate conditions and return results based on whether those conditions are true or false. These are essential for categorising data and creating conditional calculations.

For functions that help you handle empty values (nulls), see Working with empty values.

Function	Description	Example
Between	Checks if a value falls within a range	`Between([Score], 1, 10)`
Choose	Returns a value from a list based on an index number	`Choose([Month], "Jan", "Feb", "Mar", ...)`
Coalesce	Returns the first non-empty value from a list	`Coalesce([Primary Contact], [Secondary Contact])`
If	Returns different values based on a condition	`If([Emissions] > 1000, "High", "Low")`
In	Checks if a value matches any value in a list	`In([Status], "Pending", "In Progress", "Review")`
IsNotNull	Checks if a value exists (returns true or false)	`IsNotNull([Approval Date])`
IsNull	Checks if a value is empty (returns true or false)	`IsNull([Response])`
NullIf	Returns empty if two values are equal	`NullIf([Value], 0)`
Switch	Tests a value against multiple options and returns matching result	`Switch([Code], "A", "Active", "I", "Inactive", "Unknown")`
Zn	Returns the value, or 0 if the value is empty	`Zn([Quantity])`

Tip: The If function supports multiple conditions in a single formula. Instead of nesting multiple If statements, you can chain conditions: If([Value] < 100, "Low", [Value] < 500, "Medium", "High").

Maths functions

Maths functions perform calculations—rounding numbers, trigonometry, logarithms, and more.

Function	Description	Example
Abs	Returns the absolute value (removes negative sign)	`Abs([Variance])`
Acos	Returns the arccosine of a number	`Acos([Value])`
Asin	Returns the arcsine of a number	`Asin([Value])`
Atan	Returns the arctangent of a number	`Atan([Value])`
Atan2	Returns the arctangent of a coordinate pair	`Atan2([Y], [X])`
BinFixed	Assigns values to fixed-size bins	`BinFixed([Score], 10)`
BinRange	Assigns values to custom bin ranges	`BinRange([Value], 0, 50, 100, 200)`
BitAnd	Performs a bitwise AND operation	`BitAnd([Flags], 4)`
BitOr	Performs a bitwise OR operation	`BitOr([Flags1], [Flags2])`
Ceiling	Rounds up to the nearest whole number or multiple	`Ceiling([Value])`
Cos	Returns the cosine of an angle	`Cos([Angle])`
Cot	Returns the cotangent of an angle	`Cot([Angle])`
Degrees	Converts radians to degrees	`Degrees([Radians])`
DistanceGlobe	Calculates distance between coordinates on a globe	`DistanceGlobe([Lat1], [Long1], [Lat2], [Long2])`
DistancePlane	Calculates distance between points on a plane	`DistancePlane([X1], [Y1], [X2], [Y2])`
Div	Returns the integer part of a division	`Div([Total], [Count])`
Exp	Returns e raised to a power	`Exp([Growth Rate])`
Floor	Rounds down to the nearest whole number or multiple	`Floor([Value])`
Greatest	Returns the largest value from a list	`Greatest([Q1], [Q2], [Q3], [Q4])`
Int	Converts a value to an integer (rounds down)	`Int([Decimal])`
IsEven	Checks if a number is even	`IsEven([Count])`
IsOdd	Checks if a number is odd	`IsOdd([Count])`
Least	Returns the smallest value from a list	`Least([Estimate], [Actual])`
Ln	Calculates the natural logarithm	`Ln([Growth Factor])`
Log	Calculates the logarithm (base 10 by default)	`Log([Value])`
Mod	Returns the remainder after division	`Mod([Value], 12)`
MRound	Rounds to the nearest specified multiple	`MRound([Price], 0.05)`
Pi	Returns the value of π (3.14159...)	`Pi()`
Power	Raises a number to a power	`Power([Base], 2)`
Radians	Converts degrees to radians	`Radians([Degrees])`
Round	Rounds to a specified number of decimal places	`Round([Value], 2)`
RoundDown	Rounds down to a specified number of decimal places	`RoundDown([Price], 2)`
RoundUp	Rounds up to a specified number of decimal places	`RoundUp([Estimate], 0)`
RowAvg	Calculates the average of values in a row	`RowAvg([Jan], [Feb], [Mar])`
Sign	Returns -1 for negative, 0 for zero, or 1 for positive	`Sign([Change])`
Sin	Returns the sine of an angle	`Sin([Angle])`
Sqrt	Calculates the square root	`Sqrt([Area])`
Tan	Returns the tangent of an angle	`Tan([Angle])`
Trunc	Truncates a number to a specified number of decimal places	`Trunc([Value], 2)`

Text functions

Text functions help you work with text values—combining text, extracting parts of it, or transforming how it appears.

Function	Description	Example
Concat	Joins multiple text values together	`Concat([First Name], " ", [Last Name])`
Contains	Checks if text contains a specific substring	`Contains([Supplier Name], "Ltd")`
EndsWith	Checks if text ends with a specific substring	`EndsWith([Email], ".com")`
Find	Finds the position of text within a string (returns 0 if not found)	`Find([Address], "London")`
ILike	Checks if text matches a pattern (case-insensitive)	`ILike([Name], "%energy%")`
Left	Extracts characters from the start of text	`Left([Product Code], 3)`
Len	Returns the number of characters in text	`Len([Description])`
Like	Checks if text matches a pattern (case-sensitive)	`Like([Code], "UK%")`
LPad	Pads text on the left to a specified length	`LPad([ID], 6, "0")`
Lower	Converts text to lowercase	`Lower([Email])`
LTrim	Removes spaces from the start of text	`LTrim([Input])`
MD5	Creates an MD5 hash of text	`MD5([Password])`
Mid	Extracts characters from the middle of text	`Mid([Code], 2, 3)`
Proper	Capitalises the first letter of each word	`Proper([Supplier Name])`
StartsWith	Checks if text starts with a specific substring	`StartsWith([Code], "UK")`
Substring	Extracts a portion of text (same as Mid)	`Substring([Text], 1, 5)`
Trim	Removes spaces from the start and end of text	`Trim([Input])`
Upper	Converts text to uppercase	`Upper([Country])`
UrlPart	Extracts a component from a URL	`UrlPart([Link], "host")`

Type functions

Type functions convert values from one data type to another.

Function	Description	Example
Date	Converts a value to a date	`Date([Date Text])`
Json	Converts a value to JSON format	`Json([Data])`
Logical	Converts a value to true or false	`Logical([Flag])`
Number	Converts a value to a number	`Number([Amount Text])`
Text	Converts a value to text	`Text([ID])`
Variant	Converts text to a variant (flexible) data type	`Variant([JSON Text])`

Window functions

Window functions perform calculations across rows in your table, taking into account the order or position of data. These are useful for running totals, comparisons with previous periods, and rankings.

Ranking functions

Ranking functions assign positions to rows based on their values.

Function	Description	Example
Ntile	Divides rows into equal groups and assigns group numbers	`Ntile(4)`
Rank	Assigns ranks, with gaps for ties (1, 2, 2, 4)	`Rank([Total Emissions])`
RankDense	Assigns ranks without gaps for ties (1, 2, 2, 3)	`RankDense([Score])`
RankPercentile	Ranks rows by percentile	`RankPercentile([Performance])`
RowNumber	Numbers each row sequentially (1, 2, 3, 4)	`RowNumber()`
VisibilityLimit	Limits displayed values based on ranking	`VisibilityLimit([Category], 10)`

Cumulative functions

Cumulative functions calculate running totals and values that accumulate row by row.

Function	Description	Example
CumulativeAvg	Calculates a running average	`CumulativeAvg([Daily Output])`
CumulativeCorr	Calculates a running correlation between two columns	`CumulativeCorr([X], [Y])`
CumulativeCount	Counts rows up to and including the current row	`CumulativeCount([Transaction ID])`
CumulativeMax	Tracks the maximum value seen so far	`CumulativeMax([Peak Demand])`
CumulativeMin	Tracks the minimum value seen so far	`CumulativeMin([Efficiency])`
CumulativeStdDev	Calculates a running standard deviation	`CumulativeStdDev([Values])`
CumulativeSum	Calculates a running total	`CumulativeSum([Monthly Emissions])`
CumulativeVariance	Calculates a running variance	`CumulativeVariance([Measurements])`
CumeDist	Calculates the cumulative distribution of values	`CumeDist([Score])`

Shifting functions

Shifting functions let you compare the current row with previous or subsequent rows—helpful for period-over-period analysis.

Function	Description	Example
FillDown	Fills empty values with the last non-empty value	`FillDown([Category])`
First	Returns the first value in a group	`First([Opening Balance])`
FirstNonNull	Returns the first non-empty value in a group	`FirstNonNull([Response])`
Lag	Returns the value from a previous row	`Lag([Emissions], 1)`
Last	Returns the last value in a group	`Last([Closing Balance])`
LastNonNull	Returns the last non-empty value in a group	`LastNonNull([Status])`
Lead	Returns the value from a subsequent row	`Lead([Forecast], 1)`
Nth	Returns the value from a specific row position	`Nth([Value], 3)`

Working with empty values

Empty cells (called Null values) require special attention in formulas. Any arithmetic operation involving a Null value returns Null as the result.

For example, if [Scope 2 Emissions] is empty for a particular row:

[Scope 1 Emissions] + [Scope 2 Emissions]

This formula returns Null for that row, even if Scope 1 has a value.

Use these approaches to handle empty values:

IsNull function: Tests whether a value is empty: If(IsNull([Date]), "Missing", "Present")
Zn function: Converts empty values to zero: Zn([Value1]) + Zn([Value2])
Coalesce function: Returns the first non-empty value: Coalesce([Primary], [Backup], 0)

Tips for writing formulas

Reference columns by name: Put column names in square brackets: [Column Name]
Reference columns from other tables: Include the table name: [Table Name/Column Name]
Use autocomplete: Start typing a function name and select from the suggestions that appear.
Chain conditions in If: Instead of nesting If statements, list multiple conditions: If([Size] < 3, "Small", [Size] < 6, "Medium", "Large")

For step-by-step guidance on creating formulas, see Formula basics.

Create and manage tables

Sort and group data in tables

Control types reference

Formula basics

Common formulas for Excel users