Skip to content

Functions and Formulas Advanced Reference

Functions

ADDOP

The ADDOP function is used before a GROUPOPS function command to specify column operations to perform when data is grouped.

Syntax

ADDOP( column_a1, operation, [column_name] )
  • column_a1 - which column to apply the operation. Use A1 notation for columns.
  • operation - specifies what operation to apply to the column. Current available options are:
    • SUM
    • AVERAGE
    • COUNT
  • column_name (optional) - name of the grouped column

Example Usage

ADDOP( C:C, "SUM" )
ADDOP( D:D, "AVERAGE", "Average_Amt" )
GROUPOPS( A:A )

Notes

  • One or more ADDOP commands may be called before performing the GROUPOPS command.
  • If no ADDOPs are called before GROUPOPS, the evaluation will result in a warning and will only return the columns listed in GROUPOPS.

ADDCOLUMN

The ADDCOLUMN function adds a new column to the table with the formula results. The input formula is evaluated row-by-row. Any cell references in the formula automatically increment.

Syntax

ADDCOLUMN( output_column_name, formula, [column_type] )
  • output_column_name - name of the new column
  • formula - formula to be evaluated
  • column_type (optional) - new column type. Available types include:
    • INT
    • FLOAT
    • BIGINT
    • BIGFLOAT
    • DATETIME
    • TEXT

Example Usage

ADDCOLUMN( "total_amt", A1+B1, "BIGFLOAT" )
ADDCOLUMN( "avg_amt", AVERAGE(A1,A2,A3) )
ADDCOLUMN( "category", A1 & "_" & B1, "TEXT" )

Notes

  • The formula argument is entered in without the = .
  • Formula references use A1 notation. For example:
    • The reference B2 refers to the cell in Column B and row 2. Rows are 1-based.
    • The reference A:A refers to the entire Column A.
  • Currently, the A1 notation $ syntax is not supported in dataframe formulas.
  • Formulas that span across multiples rows are allowed. Simply increment the row number in the A1 notation relative to a start row.
    • For example, a moving average across 3 rows can be represented by: AVERAGE( A1, A2, A3 ).
    • References within the formula are relative to each other, so the evaluation will begin at the largest relative row to the smallest row. In this case, the results of the moving average will start at row 3.
    • This also means that AVERAGE( A1, A2, A3 ) and AVERAGE( A2, A3, A4 ) are equivalent formulas, since the relative row relationship between the A1 references is the same.
  • Formulas cannot reference the new column generated. For example, if the ADDCOLUMN function adds a new Column C, then the formula cannot include references to Column C.
  • If the column_type argument is not specified, then the new column type is set based on the result of the first row.

The CROSSLINK function defines the source data of the current Data Node. Source data is specified by the name of the incoming tab and the columns to keep.

Syntax

CROSSLINK( source_tab_name, source_column, [additional_columns]... )
  • source_tab_name - name of the source sheet tab. The source tab can be a Preview or a Dataframe.
  • source_column - column to keep from the source sheet. Use A1 notation for columns.
  • additional_columns (optional) - additional columns to keep in from the source sheet. Use A1 notation for columns.

Example Usage

CROSSLINK("Preview1", A:A)
CROSSLINK("Preview1", A:A, B:B, C:C, D:D, E:E)

Notes

  • Invalid columns will trigger a warning message and are ignored.

DEDUP

The DEDUP function removes duplicates in the data across the columns listed.

Syntax

DEDUP( column_a1, [additional_columns]... )
  • column_a1 - column to remove duplicates from. Use A1 notation for columns.
  • additional_columns (optional) - additional columns to remove duplicates from. Use A1 notation for columns.

Example Usage

DEDUP(A:A)
DEDUP(A:A, B:B, C:C, D:D)

Notes

  • Invalid columns will trigger a warning message and are ignored.
  • If no valid columns are specified, then the evaluation will return an error.

DROP

The DROP function removes rows where the specified column satisfies a condition.

Syntax

DROP( column_a1, condition )
  • column_a1 - column for checking the condition. Use A1 notation for columns.
  • condition - comparison condition to check for. Must be in double quotes. Comparison operators include:
    • = equals
    • != not equals
    • < less than
    • <= less than or equal to
    • > greater than
    • >= greater than or equal to

Example Usage

DROP(A:A, "<5")
DROP(B:B,"=apple")

Notes

  • For TEXT columns, only = and != are evaluated.
  • If the condition has no operator, then defaults to =.

DROPIF

The DROPIF function removes rows where the formula evaluates to a TRUE value.

Syntax

DROPIF( formula )
  • formula - formula to evaluate.

Example Usage

DROPIF( MOD(ROW(),3) ) # Drop every 3rd row
DROPIF( IF(A1>5, "TRUE", "FALSE") ) # Drop if the value in column A is greater than 5

Notes

  • A TRUE value is a text value of "TRUE" or any non-zero numeric value.

FILTER

The FILTER function filters for (or keeps) rows where the specified column satisfies a condition.

Syntax

FILTER( column_a1, condition )
  • column_a1 - column for checking the condition. Use A1 notation for columns.
  • condition - comparison condition to check for. Must be in double quotes. Comparison operators include:
    • = equals
    • != not equals
    • < less than
    • <= less than or equal to
    • > greater than
    • >= greater than or equal to

Example Usage

FILTER(A:A, "<5")
FILTER(B:B,"=apple")

Notes

  • For TEXT columns, only = and != are evaluated.
  • If the condition has no operator, then defaults to =.

FILTERIF

The FILTERIF function filters for (or keeps) rows where the formula evaluates to a TRUE value.

Syntax

FILTERIF( formula )
  • formula - formula to evaluate.

Example Usage

FILTERIF( MOD(ROW(),3) ) # Keep every 3rd row
FILTERIF( IF(A1>5, "TRUE", "FALSE") ) # Keep if the value in column A is greater than 5

Notes

  • A TRUE value is a text value of "TRUE" or any non-zero numeric value.

GROUPAVERAGE

The GROUPAVERAGE function groups the data by the specified column(s) and averages all other numeric columns.

Syntax

GROUPAVERAGE( column_a1, [additional_columns]... )
  • column_a1 - column to group by. Use A1 notation for columns.
  • additional_columns (optional) - additional columns to group by

Example Usage

GROUPAVERAGE(A:A)
GROUPAVERAGE(A:A, B:B, C:C)

Notes

  • NULL values are excluded.
  • Invalid columns will trigger a warning message and are ignored.
  • If no valid columns are specified, then the evaluation will return an error.

GROUPCOUNT

The GROUPCOUNT function groups the data by the specified column(s) and returns the count of all other numeric columns.

Syntax

GROUPCOUNT( column_a1, [additional_columns]... )
  • column_a1 - column to group by. Use A1 notation for columns.
  • additional_columns (optional) - additional columns to group by

Example Usage

GROUPCOUNT(A:A)
GROUPCOUNT(A:A, B:B, C:C)

Notes

  • NULL and BLANK values are excluded.
  • Invalid columns will trigger a warning message and are ignored.
  • If no valid columns are specified, then the evaluation will return an error.

GROUPSUM

The GROUPSUM function groups the data by the specified column(s) and returns the count of all other numeric columns.

Syntax

GROUPSUM( column_a1, [additional_columns]... )
  • column_a1 - column to group by. Use A1 notation for columns.
  • additional_columns (optional) - additional columns to group by

Example Usage

GROUPSUM(A:A)
GROUPSUM(A:A, B:B, C:C)

Notes

  • NULL values are excluded.
  • Invalid columns will trigger a warning message and are ignored.
  • If no valid columns are specified, then the evaluation will return an error.

GROUPOPS

The GROUPOPS function groups the records by the given columns and applies the operations specified by prior ADDOP commands.

Syntax

GROUPOPS( column_a1, [additional_columns]... )
  • column_a1 - column to group by. Use A1 notation for columns.
  • additional_columns (optional) - additional columns to group by

Example Usage

ADDOP(C:C, "SUM")
ADDOP(D:D, "AVERAGE", "Average_Amt")
GROUPOPS(A:A, B:B)

Notes

  • Only preceding the ADDOP commands after any prior GROUPOPS are applied on the current GROUPOPS.

PIVOT

The PIVOT function performs a two-dimensional grouping of categorical variables and aggregates values in the group based on the specified operation. In the resulting dataset, the first category will displayed on the y axis (going down rows). The second category will be displayed on the x axis (going across columns). This allows you to access a value pertaining to two categories given by the row and column.

For example, suppose our data looks like the following:

Color Size Amt
navy S 5
navy M 5
navy L 5
black M 5
black M 5
white S 5
white L 5

If we apply a pivot on Color and Size where we return the total Amt, our formula would look like: PIVOT(A:A,B:B,C:C,"SUM"). The resulting table would look like:

Color L_Amt M_Amt S_Amt
black 0 10 0
navy 5 5 5
white 5 0 5

Syntax

PIVOT( row_field, col_field, value_field, [operation] )
  • row_field - input column to use for the y axis category (going down rows). Use A1 notation for columns.
  • col_field - input column to use for the x axis category (going across columns). Use A1 notation for columns.
  • value_field - input column of data to aggregate.
  • operation (optional) - indicates the operation to perform when grouping the value_field . Available operations include:
    • AVERAGE - averages all the numbers within the group. NULLs are excluded.
    • COUNT - counts all the values within the group. NULLs and BLANKs are excluded.
    • SUM - takes the sum of all the numbers within the group. NULLs are excluded.
    • VALUE - keeps the first value within the group.

Example Usage

PIVOT(A:A, B:B, C:C, "SUM")
PIVOT(A:A, B:B, C:C, "AVERAGE")
PIVOT(A:A, B:B, C:C)

Notes

  • If the operation argument is not specified, then the default is VALUE.

SORT

The SORT function sorts the records by the specified columns in ascending order.

Syntax

SORT( column_a1, [additional_columns]... )
  • column_a1 - column to sort by. Use A1 notation for columns.
  • additional_columns (optional) - additional columns to sort by

Example Usage

SORT(A:A)
SORT(A:A, B:B)

Notes

  • The SORT function is a stable sort.

SORTDESCENDING

The SORTDESCENDING function sorts the records by the specified columns in descending order.

Syntax

SORTDESCENDING( column_a1, [additional_columns]... )
  • column_a1 - column to sort by. Use A1 notation for columns.
  • additional_columns (optional) - additional columns to sort by

Example Usage

SORTDESCENDING(A:A)
SORTDESCENDING(A:A, B:B)

Notes

  • The SORTDESCENDING function is a stable sort.

Formulas

Numeric Operators

The following is a list of operators that can be used with numeric inputs.

Operator Description Input Result Value
+ addition operator =5+2 7
- subtraction operator =5-2 3
* multiplication operator =5*2 10
/ division operator =5/2 2.5
^ power operator =5^2 25

Comparison Operators

The following is a list of comparison operators that can be used for numeric and text comparisons. Text comparisons only support = and <>.

Operator Description Input Result Value
= equals to =2=5
="apples"="bananas"		 FALSE
FALSE
<> not equals to =2<>5
="apples"<>"bananas"		 TRUE
TRUE
< less than =2<5 TRUE
<= less than or equal to =2<=5 TRUE
> greater than =2>5 FALSE
>= greater than or equal to =2>=5 FALSE

Text Operators

Operator Description Input Result Value
& concatenate operator ="Row"&64 Row64

ABS

The ABS formula returns the absolute value of a number.

Syntax

ABS( number )
  • number - input number (any real number).

Return Value: Number

Example Usage

Input Result
=ABS(5) 5
=ABS(-2) 2

ACOS

The ACOS formula returns the arccosine or inverse cosine of a number. The returned angle is in radians from 0 to π.

Syntax

ACOS( number )
  • number - input number. Must be from -1 to 1.

Return Value: Number

Example Usage

Input Result
=ACOS(0.5) 1.047198
=ACOS(-0.5) 2.094395
=ACOS(0) 1.570796

ACOSH

The ACOSH formula returns the inverse hyperbolic cosine of a number.

Syntax

ACOSH( number )
  • number - input number. Must be greater than or equal to 1.

Return Value: Number

Example Usage

Input Result
=ACOSH(2) 1.316958
=ACOSH(1) 0

AND

The AND formula returns TRUE if all the provided conditions are true, and FALSE if any of the conditions are false.

Syntax

AND( condition, [additional_conditions]... )
  • condition - a formula condition that evaluates to a number, TRUE, or FALSE.
  • additional_conditions (optional) - additional formula conditions.

Return Value: TRUE or FALSE

Example Usage

Input Result
=AND(1) TRUE
=AND(0) FALSE
=AND(5>3,SUM(1,2)=3) TRUE

Notes

  • Each condition argument must evaluate to a number, TRUE, or FALSE.
  • Non-zero numbers are considered TRUE.
  • Text, NULL, or BLANK values are ignored.

ASIN

The ASIN formula returns the arcsine or inverse sine of a number. The returned angle is in radians from -π/2 to π/2.

Syntax

ASIN( number )
  • number - input number. Must be from -1 to 1.

Return Value: Number

Example Usage

Input Result
=ASIN(0.5) 0.523599
=ASIN(-0.5) -0.523599
=ASIN(0) 0

ASINH

The ASINH formula returns the inverse hyperbolic sine of a number.

Syntax

ASINH( number )
  • number - input number (any real number).

Return Value: Number

Example Usage

Input Result
=ASINH(1) 0.881374
=ASINH(-1) -0.881374
=ASINH(0) 0

ATAN

The ATAN formula returns the arctangent or inverse tangent of a number. The returned angle is in radians from -π/2 to π/2.

Syntax

ATAN( number )
  • number - input number (any real number).

Return Value: Number

Example Usage

Input Result
=ATAN(1) 0.785398
=ATAN(-1) -0.785398
=ATAN(0) 0

ATANH

The ATANH formula returns the inverse hyperbolic tangent of a number.

Syntax

ATANH( number )
  • number - input number. Must be between -1 to 1 (excluding 1 and -1).

Return Value: Number

Example Usage

Input Result
=ATANH(0.5) 0.549306
=ATANH(-0.5) -0.549306
=ATANH(0) 0

AVERAGE

The AVERAGE formula returns the average (mean) of the input values.

Syntax

AVERAGE( value, [additional_values]... )
  • value - input number
  • additional_values (optional) - additional input numbers

Return Value: Number

Example Usage

Data

A B
1 2
2 apple
3 5

Calculations

Input Result
=AVERAGE(1, 2) 1.5
=AVERAGE(A1:A3) 3.5

Notes

  • All NULL and non-numeric values are ignored in the calculation.

AVERAGEA

The AVERAGEA formula returns the average (mean) of the input values, including non-numeric and NULL values in the count.

Syntax

AVERAGEA( value, [additional_values]... )
  • value - input number.
  • additional_values (optional) - additional input numbers

Return Value: Number

Example Usage

Data

A B
1 2
2 apple
3 5

Calculations

Input Result
=AVERAGEA(1, 2) 1.5
=AVERAGEA(A1:A3) 2.333333

Notes

  • Includes NULL and non-numeric values as part of the count when taking the average.

AVERAGEIF

The AVERAGEIF formula returns the average (mean) of the input values if the given criteria is satisfied.

Syntax

AVERAGEIF( range, criteria, [average_range] )
  • range - range of values on which to test the criteria
  • criteria - criteria or condition to check for. This is a quoted expression that begins with a comparison operators: =, <>, <, <=, >, >=.
  • Examples: "=abc" or "<100"
  • If the criteria expression does not begin with an operator, then it is assumed to be =.
  • [average_range] (optional) - range of values on which to take the average

Return Value: Number

Example Usage

Data

A B
1 2 4
2 apple 3
3 5 2
4 6 7

Calculations

Input Result
=AVERAGEIF(B1:B4,"<5") 3
=AVERAGEIF(A1:A4,"<>apple",B1:B4) 4.333333

Notes

  • NULL and non-numeric values in the average range are ignored.

AVERAGEIFS

The AVERAGEIFS formula returns the average (mean) of the input values depending on multiple criteria.

Syntax

AVERAGEIFS( average_range, range1, criteria1, range2, criteria2, ...)
  • average_range - range to average
  • range1 - range to test criteria1
  • criteria1 - criteria to check on range1. This is a quoted expression that begins with a comparison operators: =, <>, <, <=, >, >=.
    • Examples: "=abc" or "<100"
    • If the criteria expression does not begin with an operator, then it is assumed to be =.
  • range2 - range to test criteria2
  • criteria2 - criteria to check on range2

Return Value: Number

Example Usage

Data

A B
1 2 4
2 apple 3
3 5 2
4 6 7

Calculations

Input Result
=AVERAGEIFS(B1:B4,B1:B4,"<5",A1:A4,"<>apple") 3
=AVERAGEIFS(A1:A4,B1:B4,">2",A1:A4,">4") 6

Notes

  • NULL and non-numeric values in the average range are ignored.

CEILING

CEILING function rounds a number up to the nearest multiple factor.

Syntax

CEILING( number, factor )
  • number - number to round
  • factor - multiple to round by

Return Value: Number

Example Usage

Input Result
=CEILING(7.25,0.1) 7.3
=CEILING(0.123, 0.01) .13

CHAR

CHAR returns a single character when given an ASCII, ANSI, or Unicode table index.

Syntax

CHAR( number )
  • number - index in ASCII or Unicode table

Return Value: Single Character

Example Usage

Input Result
=CHAR(82) R
=CHAR(64) @

CHOOSE

CHOOSE uses an index to pick from a list of values.

Syntax

CHOOSE( index, value1, value2, ... )
  • index - index in list
  • value1 - value returned if index = 1
  • value2 - value returned if index = 2
  • etc.

Return Value: Text of Value

Example Usage

Input Result
=CHOOSE(3,"Row64", "Is", "Awesome") Awesome
=CHOOSE(3, 62,63,64,65) 64

CLEAN

CLEAN removes all non-printable ASCII characters.

Syntax

CLEAN( text )
  • text - input text

Return Value: Cleaned Text

Example Usage

Input Result
=CLEAN("Hello Row64" & CHAR(10)) Hello Row64
=CLEAN("64_" & CHAR(21) & "64") 64_64

CODE

CODE returns the ASCII or Unicode index from the first character of text.

Syntax

CODE( text )
  • text - input text

Return Value: ASCII or Unicode index of first character

Example Usage

Input Result
=CODE("Row64") 82
=CODE("R") 82

COLUMN

COLUMN returns the index of the formula column location. It is 1-based; A1 returns 1, B1 returns 2, etc.

Syntax

COLUMN()

Return Value: Index of column

Example Usage

For this example, assume the formula is in cell B10.

Input Result
=COLUMN() 2

CONCAT

CONCAT combines together multiple inputs into a text string.

Syntax

CONCAT( text1, text2, ... )
  • text1 - first text, number, or cell range
  • text2 - second text, number, or cell range
  • etc.

Return Value: Combined text

Example Usage

Input Result
=CONCAT("Row",6,4) Row64
=CONCAT("b",2,"b") b2b

COUNT

COUNT returns the number of numeric values in a range or list of values.

Syntax

COUNT( input1, input2, ... )
  • input1 - first number, cell range, or text
  • input2 - second number, cell range, or text
  • etc.

Return Value: Number

Example Usage

Assuming cells A1 to A4 are numeric values.

Input Result
=COUNT("Row",6,4) 2
=COUNT(A1:A4,1,"Text") 5

COUNTA

COUNTA returns the number of values in a range or list of values.

Syntax

COUNTA( input1, input2, ... )
  • input1 - first number, cell range, or text
  • input2 - second number, cell range, or text
  • etc.

Return Value: Number

Example Usage

Assuming cells A1 to A4 are numeric values.

Input Result
=COUNTA("Row",6,4) 3
=COUNTA(A1:A4,1,"Text") 6

COUNTBLANK

COUNTBLANK returns the number of blank values in a range or list of values.

Syntax

COUNTA( input1, input2, ... )
  • input1 - first number, cell range or text
  • input2 - second number, cell range or text
  • etc.

Return Value: Number

Example Usage

Assuming cells A1 to A4 are blank cells.

Input Result
=COUNTBLANK("Row","",64) 1
=COUNTBLANK(A1:A4,1,"Text") 4

COUNTIF

The COUNTIF formula returns the count of the input values if the given criteria is satisfied.

Syntax

COUNTIF( range, criteria )
  • range - range of values on which to test the criteria
  • criteria - criteria or condition to check for. This is a quoted expression that begins with a comparison operators: =, <>, <, <=, >, >=.
  • Examples: "=abc" or "<100"
  • If the criteria expression does not begin with an operator, then it is assumed to be =.

Return Value: Number

Example Usage

Data

A B
1 2 4
2 apple 3
3 5 2
4 6 7

Calculations

Input Result
=COUNTIF(B1:B4,">3") 2
=COUNTIF(A1:A4,"<>apple") 3

Notes

  • NULL and non-numeric values are ignored.

COUNTIFS

The COUNTIFS formula returns the count of the input values, depending on multiple criteria.

Syntax

COUNTIFS( range1, criteria1, range2, criteria2, ...)
  • range1 - range to test criteria1
  • criteria1 - criteria to check on range1. This is a quoted expression that begins with a comparison operators: =, <>, <, <=, >, >=.
  • Examples: "=abc" or "<100"
  • If the criteria expression does not begin with an operator, then it is assumed to be =.
  • range2 - range to test criteria2
  • criteria2 - criteria to check on range2
  • etc.

Return Value: Number

Example Usage

Data

A B
1 2 4
2 apple 3
3 5 2
4 6 7

Calculations

Input Result
=COUNTIFS(B1:B4,"<5",A1:A4,"<>apple") 2
=COUNTIFS(B1:B4,">3",A1:A4,">4") 1

Notes

  • NULL and non-numeric values in the average range are ignored.

COS

The COS formula returns the cosine of a number.

Syntax

COS( number )
  • number - input number

Return Value: Number

Example Usage

Input Result
=COS(2 * PI()) 1
=COS(PI()*.25) 0.707107

COSH

The COSH formula returns the hyperbolic cosine of a number.

Syntax

COSH( number )
  • number - input number

Return Value: Number

Example Usage

Input Result
=COSH(2) 3.762196
=COSH(0) 1

COT

The COT formula returns the cotangent of an angle

Syntax

COT( number )
  • number - angle in radians

Return Value: Number

Example Usage

Input Result
=COT(1) 0.642093
=COT(PI()*.25) 1

CSC

Returns the cosecant of an angle.

Syntax

CSC( number )
  • number - angle in radians

Return Value: Number

Example Usage

Input Result
=CSC(1) 1.188395
=CSC(PI()*.5) 1

DATE

The CSC formula converts a year, month, and day input into a date.

Syntax

DATE( year, month, day )
  • year - year of a date
  • month - month of a date
  • day - day of a date

Return Value: Date Number

Example Usage

Assumes cell has been formated as a date / time

Input Result
=DATE(2000,1,1) 1/1/2000
=DATE(2020,10,25) 10/25/2020

DATEVALUE

The DATEVALUE formula converts a date string into a spreadsheet date number.

Syntax

DATEVALUE( text )
  • text - text to be converted to a date

Return Value: Date Number

Example Usage

Assumes cell has been formated as a date / time

Input Result
=DATEVALUE("2011-7-20") 7/20/2011
=DATEVALUE("8/18/2018") 8/18/2018

DAY

The DAY formula returns the day of a date.

Syntax

DAY( date, number )
  • date number - date to extract the day from

Return Value: Date Number

Example Usage

Input Result
=DAY(DATEVALUE("8/18/2018")) 18
=DAY(DATEVALUE("2011-7-20")) 20

DOLLAR

The DOLLAR formula converts a number into text describing currency.

Syntax

DOLLAR( number, [decimals] )
  • number - amount
  • decimals (optional) - number of decimal places

Return Value: Date Number

Example Usage

Input Result
=DOLLAR(1000) $1,000.00
=DOLLAR(1000,0) $1,000

DEGREES

The DEGREES formula converts from radians to degrees.

Syntax

DEGREES( number)
  • number - angle in radians

Return Value: Number

Example Usage

Input Result
=DEGREES(PI()) 180
=DEGREES(PI()*-.25) -45

EDATE

The EDATE formula returns a date a specified number of months before or after a date.

Syntax

EDATE( date, months)
  • date - date number
  • months - number of months forward or backwards

Return Value: Date Number

Example Usage Assumes cell has been formated as a date / time

Input Result
=EDATE(DATE(2000,1,1), 10) 11/1/2000
=EDATE(DATEVALUE("2011-7-20"),2) 9/20/2011

EXP

The EXP formula returns e (Euler's number) raised to the power of a number.

Syntax

EXP( number )
  • number - exponent applied to the base e

Return Value: Number

Example Usage

Input Result
=EXP(1) 2.718282
=EXP(10) 22026.465795

EVEN

The EVEN formula rounds up to the nearest even integer.

Syntax

EVEN( number )
  • number - number to round up to even

Return Value: Number

Example Usage

Input Result
=EVEN(1) 2
=EVEN(10) 10

EXACT

The EXACT formula compares 2 inputs and returns TRUE if they are the same.

Syntax

EXACT( value1, value2 )
  • value1 - text or number to compare
  • value2 - text or number to compare

Return Value: TRUE or FALSE

Example Usage

Input Result
=EXACT(10,10) TRUE
=EXACT("Row64","Row24") FALSE

EXCELTIME

The EXCELTIME formula converts Python time to Excel Spreadsheet time.

Python time is nanoseconds from the UNIX Epoch January 1, 1970.

In Excel time, the integer part represents days, since January 1, 1900, and the decimal part represents time.

Syntax

EXCELTIME( python_time )
  • python_time - number in python time to convert

Return Value: Number

Example Usage Assumes cell has been formated as a date / time

Input Result Reference Date
=EXCELTIME(0) 1/1/1970 Unix Epoch
=EXCELTIME(PYTHONTIME(DATEVALUE("2011-7-20"))) 7/20/2011

FACT

The FACT formula returns the factorial of a number.

Syntax

FACT( number )
  • number - number to calculate factorial on

Return Value: Number

Example Usage

Input Result
=FACT(4) 24
=FACT(10) 3628800

FIND

The FIND formula gets the index of the first character in a string that matches the search. It is case-sensitive.

Syntax

FIND( find_text, within_text )
  • find_text - text to search for
  • within_text - text to run the search on

Return Value: Number

Example Usage

Input Result
=FIND("needle","haystack and needle") 14
=FIND("hello","hello Row64") 1

FLOOR

The FLOOR formula rounds a number down to a specified number of decimal places.

Syntax

FLOOR( number, significance )
  • number - amount
  • significance - multiple for rounding

Return Value: Number

Example Usage

Input Result
=FLOOR(2.4123, 1) 2
=FLOOR(2.4123, .01) 2.41

FUZZY_MATCH

The FUZZY_MATCH formula runs a detailed similarity comparision between two texts and returns a score between 0-100. It is a type of Fuzzy Search.

Consider using FUZZY_QMATCH for larger datasets.

Syntax

FUZZY_MATCH( text1, text2 )
  • text1 - text
  • text2 - text to compare with

Return Value: Number score between 0 - 100

Example Usage

Input Result
=FUZZY_MATCH("hello","helllo!") 83.333333
=FUZZY_MATCH("hello","howdy howdy") 25

FUZZY_QMATCH

The FUZZY_QMATCH formula runs a quick similarity comparision between two texts and returns a score between 0-100. It is a type of Fuzzy Search.

Consider FUZZY_MATCH for smaller datasets where high detail is required.

Syntax

FUZZY_QMATCH( text1, text2 )
  • text1 - text
  • text2 - text to compare with

Return Value: Number score between 0 - 100

Example Usage

Input Result
=FUZZY_QMATCH("hello","helllo!") 83.333333
=FUZZY_QMATCH("hello","howdy howdy") 25

FUZZY_CONTAINS

The FUZZY_CONTAINS formula returns a similarity score (between 0 - 100) for a search text being scanned within a text. It is a type of Fuzzy Search.

Syntax

FUZZY_CONTAINS( search_text, text )
  • search_text - text to search for
  • text - text to run the search on

Return Value: Number score between 0 - 100

Example Usage

Input Result
=FUZZY_CONTAINS("abcd","000ab0c00") 75
=FUZZY_CONTAINS("r64","learn more row64") 80

FUZZY_REPLACE

The FUZZY_REPLACE formula runs a approximate search and replaces the matching search text if it passes a threshold. It is a type of Fuzzy Search.

Syntax

RE_FUZZY_REPLACE( expression, text, replacement )
  • expression - regex expression
  • threshold - threshold for replacement (0 - 100)
  • text- text on which to apply the search
  • replacement- text used to replace the pattern

Return Value: Text

Example Usage

Input Result
=FUZZY_REPLACE("abc","000a0bc000", "####") 000####000
=FUZZY_REPLACE("haystack",60, "needle in haystck", "the hay") needle in the hay

GCD

The GCD formula returns greatest common divisor of multiple integers

Syntax

GCD( number1, [number2, ...] )
  • number1 - input integer 1
  • etc.

Return Value: Number

Example Usage

Input Result
=GCD(3,6,18) 3
=GCD(14,70,161,427,49) 7

HOUR

The HOUR formula returns the HOUR of a date time in 24 hour time.

Syntax

DAY( date_number )
  • date_number - date time to extract the hour from

Return Value: Hour Number

Example Usage

Input Result
=HOUR(DATEVALUE("8/18/2018 1:00 PM")) 13
=HOUR(DATEVALUE("8/18/2018 5:00AM")) 5

IF

The IF formula returns one value if an expression is TRUE, or another if FALSE

Syntax

IF( expression, true_value, false_value )
  • expression - expression or cell reference to evaluate
  • true_value - value returned if TRUE
  • false_value - value returned if FALSE

Return Value: Text or Number, depending on returned argument type

Example Usage

Input Result
=IF("a"="a","Got Match", "No Match") Got Match
=IF(1=2,100,0) 0

IFERROR

The IFERROR formula takes an expression and returns the result of the expression if there is no error. If there is an error, it returns a second argument.

Syntax

IFERROR( expression, error )
  • expression - expression to evaluation and return if there's no error
  • error - value to return if there is an error

Return Value: Text or Number, depending on returned argument type

Example Usage

Input Result
=IFERROR(1/0,"Error") Error
=IFERROR(1000+1,"Error") 1001

INDEX

The INDEX formula returns a cell result, given a range and row number with an optional column number for 2D ranges.

Syntax

INDEX( reference, row_number, [column_number] )
  • reference - reference to a range of cells
  • row_number - index of row
  • column_number - (optional) index of column, 1 by default

Return Value: Text or Number, depending on returned value

Example Usage

Data

A B
1 2 4
2 apple 3
3 5 2
4 6 7

Calculations

Input Result
=INDEX(A1:A4,2) apple
=INDEX(A1:B4,4,2) 7

INT

The INT formula rounds down a number to the closest integer.

Syntax

INT( number )
  • number1 - number to round down to closest integer

Return Value: Number

Example Usage

Input Result
=INT(6.4) 6
=INT(7.999964) 7

ISBLANK

The ISBLANK formula takes a reference cell and return TRUE if it's blank or empty.

Syntax

ISBLANK( reference )
  • reference - cell reference

Return Value: TRUE or FALSE

Example Usage

Assuming cell A1 is empty

Input Result
=ISBLANK(A1) TRUE
=ISBLANK("Hello") FALSE

ISEMAIL

The ISEMAIL formula takes text and returns TRUE if it's an email.

Syntax

ISEMAIL( text )
  • text - text to check for email

Return Value: TRUE or FALSE

Example Usage

Input Result
=ISEMAIL("support@row64.com") TRUE
=ISEMAIL("some text") FALSE

ISERROR

The ISERROR formula takes an expression and returns TRUE if there's an error and FALSE if there's no error.

Syntax

IFERROR( expression )
  • expression - expression to evaluate

Return Value: TRUE or FALSE

Example Usage

Input Result
=ISERROR(1/0) TRUE
=ISERROR(1000+1) FALSE

ISEVEN

The ISEVEN formula takes a number and returns TRUE if it's even, and FALSE if it's not.

Syntax

ISEVEN( number )
  • number - number to evaluate

Return Value: TRUE or FALSE

Example Usage

Input Result
=ISEVEN(7) FALSE
=ISEVEN(64) TRUE

ISNONTEXT

The ISNONTEXT formula takes a value and returns TRUE if it's not text, and FALSE if it's text.

Syntax

ISNONTEXT( value )
  • value - value to evaluate

Return Value: TRUE or FALSE

Example Usage

Input Result
=ISNONTEXT(64) TRUE
=ISNONTEXT("Row64") FALSE

ISNULL

The ISNULL formula takes a value and returns TRUE if it's NULL and FALSE if it's not. You will likely check for NULL values more in dataframe formulas than spreadsheets.

Syntax

ISNULL( value )
  • value - value or reference to evaluate

Return Value: TRUE or FALSE

Example Usage

Input Result
=ISNULL("Row64") FALSE
=ISNULL(64) FALSE

ISNUMBER

The ISNUMBER formula takes a value and returns TRUE if it's a number and FALSE if it's not.

Syntax

ISNUMBER( value )
  • value - value to evaluate

Return Value: TRUE or FALSE

Example Usage

Input Result
=ISNUMBER("Row64") FALSE
=ISNUMBER(64) TRUE

ISODD

The ISODD formula takes a number and returns TRUE if it's odd and FALSE if it's not.

Syntax

ISODD( number )
  • number - number to evaluate

Return Value: TRUE or FALSE

Example Usage

Input Result
=ISODD(7) TRUE
=ISODD(64) FALSE

ISTEXT

The ISTEXT formula takes a value and returns TRUE if it's text and FALSE if it's not.

Syntax

ISTEXT( value )
  • value - value to evaluate

Return Value: TRUE or FALSE

Example Usage

Input Result
=ISTEXT("Row64") TRUE
=ISTEXT(64) FALSE

LEFT

The LEFT formula takes text and returns a substring, starting on the left side, and gathers a specified number of characters.

Syntax

LEFT( text, [nb_characters] )
  • text - text to modify
  • nb_characters - (optional) number of characters forward from the left side

Return Value: text

Example Usage

Input Result
=LEFT("Row64",3) Row
=LEFT("AwesomeStuff",7) Awesome

LEN

The LEN formula returns the numeric length of an inputted string.

Syntax

LEN( text )
  • text - text to evaluate

Return Value: number

Example Usage

Input Result
=LEN("Row64") 5
=LEN("Awesome") 7

LCM

The LCM formula returns the least common multiple of one or more integers.

Syntax

LCM( value1, [value2, ...])
  • value1 - integer for evaluation
  • value2, ... - additional integers for evaluation

Return Value: number

Example Usage

Input Result
=LCM(6,4) 12
=LCM(14, 7, 22) 154

LN

The LN formula returns logarithm of a number based on e (2.71828182845).

Syntax

LN( number )
  • number - number for evaluation, must be positive

Return Value: number

Example Usage

Input Result
=LN(10) 2.302585
=LN(64) 4.158883

LOG

The LOG formula returns the logarithm of a given base.

Syntax

LOG( number, base )
  • number - number for evaluation, must be positive
  • base - base for the log calculation

Return Value: Number

Example Usage

Input Result
=LOG(100,10) 2
=LOG(64,2) 6

LOG10

The LOG10 formula returns the logarithm, using base 10.

Syntax

LOG10( number )
  • number - number for evaluation, must be positive

Return Value: number

Example Usage

Input Result
=LOG10(100) 10
=LOG10(64) 1.80618

LOOKUP

The LOOKUP formula searches for a key value in a range. It returns the value of the key, or if a result range is given, it returns the corresponding value in that range.

Syntax

LOOKUP( search_value, range, [result_range] )
  • search_value - value to search for
  • range - range to search
  • result_range - (optional) result range to get a corresponding value

Return Value: text or number

Example Usage

Data

Level Product
1 100 Row64 Plus
2 200 Row64 Pro
3 300 Row64 Unlimited
4 400 Row64 Light Speed

Calculations

Input Result
=LOOKUP(300,A1:A4,B1:B4) Row64 Unlimited
=LOOKUP("Row64 Light Speed",B1:B4,A1:A4) 400

LOWER

The LOWER formula converts text into lower case.

Syntax

LOWER( text )
  • text - text to lower

Return Value: text

Example Usage

Input Result
=LOWER("Row64") row64
=LOWER("AwEsOmE") awesome

MATCH

The MATCH formula searches for a key value in a range. It returns the index of the key within that range.

Syntax

LOOKUP( search_value, range, [result_range] )
  • search_value - value to search for
  • range - range to search
  • match_type - (optional):
    • 1 = assumes range is sorted acending (default)
    • 0 = exact match
    • -1 = assumes range is sorted descending

Return Value: number - index of match

Example Usage

Data

Rank Popularity Row64 Feature
1 9 499212 GeoAnalysis
2 11 348945 Real-time Streaming
3 12 246745 Interactive Slider
4 15 115312 GPU Compute

Calculations

Input Result
=MATCH(15,A1:A4) 4
=MATCH("Real-time Streaming",C1:C4) 2

MAX

The MAX formula returns the value with the highest number from a list.

Syntax

MAX( value1, [value2, ...] )
  • value1 - number for evaluation
  • value2, ... (optional) - additional number for evaluation

Return Value: number

Example Usage

Input Result
=MAX(100,10) 100
=MAX(64,6.4,640) 640

MAXIFS

The MAXIFS formula returns the maximum of the input values, depending on multiple criteria.

Syntax

MAXIFS( range, range1, criteria1, range2, criteria2, ...)
  • range - range to get maximum
  • range1 - range to test criteria1
  • criteria1 - criteria to check on range1. This is a quoted expression that begins with a comparison operators: =, <>, <, <=, >, >=.
  • Examples: "=abc" or "<100"
  • If the criteria expression does not begin with an operator, then it is assumed to be =.
  • range2 - range to test criteria2
  • criteria2 - criteria to check on range2

Return Value: Number

Example Usage

Data

A B
1 2 4
2 Row64 3
3 5 2
4 6 7

Calculations

Input Result
=MAXIFS(B1:B4,B1:B4,"<5",A1:A4,"<>Row64") 4
=MAXIFS(A1:A4,B1:B4,">2",A1:A4,">4") 6

Notes

  • NULL and non-numeric values in the range are ignored.

MEDIAN

The MEDIAN formula returns the median value from a list of numbers.

Syntax

MAX( value1, [value2, ...] )
  • value1 - number for evaluation
  • value2, ... (optional) - additional number for evaluation

Return Value: number

Example Usage

Input Result
=MEDIAN(6,4,64) 6
=MEDIAN(6,4,64,640,6400) 64

MIN

The MIN formula returns the minimum value from a list of numbers.

Syntax

MAX( value1, [value2, ...] )
  • value1 - number for evaluation
  • value2, ... (optional) - additional number for evaluation

Return Value: number

Example Usage

Input Result
=MIN(6,4,64) 4
=MIN(64,640,6400) 64

MINIFS

The MINIFS formula returns the minimum of the input values, depending on multiple criteria.

Syntax

MINIFS( range, range1, criteria1, range2, criteria2, ...)
  • range - range to get maximum
  • range1 - range to test criteria1
  • criteria1 - criteria to check on range1. This is a quoted expression that begins with a comparison operators: =, <>, <, <=, >, >=.
  • Examples: "=abc" or "<100"
  • If the criteria expression does not begin with an operator, then it is assumed to be =.
  • range2 - range to test criteria2
  • criteria2 - criteria to check on range2.

Return Value: Number

Example Usage

Data

A B
1 2 4
2 Row64 3
3 5 2
4 6 7

Calculations

Input Result
=MINIFS(B1:B4,B1:B4,"<5",A1:A4,"<>Row64") 2
=MINIFS(A1:A4,B1:B4,">2",A1:A4,">4") 6

Notes

  • NULL and non-numeric values in the range are ignored.

MINUTE

The MINUTE formula returns the minute of a date time.

Syntax

MINUTE( date_number )
  • date_number - date time to extract the minute from

Return Value: Minute Number

Example Usage

Input Result
=MINUTE(DATEVALUE("8/18/2018 1:06 PM")) 6
=MINUTE(DATEVALUE("8/18/2018 5:27AM")) 27

MOD

The MOD formula returns the result of modulo, the remainder after a division.

Syntax

MOD( dividend, divisor )
  • dividend - number for evaluation
  • divisor - number to divide by

Return Value: number

Example Usage

Input Result
=MOD(64,7) 1
=MOD(500,10) 0

MODE

The MODE formula returns the common value from a list of numbers.

Syntax

MAX( value1, [value2, ...] )
  • value1 - number for evaluation
  • value2, ... (optional) - additional number for evaluation

Return Value: number

Example Usage

Input Result
=MODE(6,64,4,64) 64
=MODE(0,1,0,1,0,1,1,1) 1

MONTH

The MONTH formula returns the month of a date time.

Syntax

MONTH( date_number )
  • date_number - date time to extract the month from

Return Value: MONTH Number

Example Usage

Input Result
=MONTH(DATEVALUE("8/18/2018")) 8
=MONTH(DATEVALUE("1/30/2018")) 1

MROUND

The MROUND formula rounds to the nearest integer with a specified decimal places factor.

Syntax

MROUND( number, significance )
  • number - amount
  • significance - multiple for rounding

Return Value: Number

Example Usage

Input Result
=MROUND(2.4123, 1) 2
=MROUND(2.6193, .01) 2.62

NEG

The NEG formula multiplies a number by -1.

Syntax

NEG( number )
  • number - value to multiply by -1

Return Value: Number

Example Usage

Input Result
=NEG(100) -100
=NEG(-1) 1

NOT

The NOT formula returns the opposite how an expression evaluates. If TRUE, it returns FALSE. If FALSE, it returns TRUE.

Syntax

NOT( expression )
  • expression - expression or cell reference to evaluate

Return Value: TRUE or FALSE

Example Usage

Input Result
=NOT(TRUE) FALSE
=NOT(1=2) TRUE

NOW

The NOW formula returns current date and time.

Syntax

NOW()

Return Value: Datetime number

Example Usage

Assumes cell has been formated as a date / time

Input Result
=NOW() 9/22/2025 2:24 PM
=NOW() + 20 10/12/2025 2:24 PM

ODD

The ODD formula rounds up to the nearest odd integer.

Syntax

ODD( number )
  • number - number to round up to odd

Return Value: Number

Example Usage

Input Result
=ODD(2) 3
=ODD(6.1) 7

OR

The OR formula returns TRUE if any expression in a list is TRUE. Otherwise, FALSE.

Syntax

OR( expression1, [expression2 ...] )
  • expression1 - expression or cell reference
  • expression2 ... - (optional) expression or cell reference

Return Value: TRUE or FALSE

Example Usage

Input Result
=OR(FALSE, FALSE, TRUE) TRUE
=OR(1=2,1=3,1=4) FALSE

PRODUCT

The PRODUCT formula multiplies a list of numbers together.

Syntax

PRODUCT( number1, [number2 ...] )
  • number1 - number to multiply
  • number2 ... - (optional) more numbers to multiply

Return Value: Number

Example Usage

Input Result
=PRODUCT(1,2,3) 6
=PRODUCT(2,4,8) 64

PERCENTILE

The PERCENTILE formula returns the percentile score, or k-th percentile, given a range and a percentile value.

Syntax

PERCENTILE( data, percentile )
  • data - range or list of values to evaluate
  • percentile - the percentile to calculate with the values

Return Value: Number

Data

A
1 10
2 24
3 50
4 64

Calculations

Input Result
=PERCENTILE(A1:A4,.3) 22.6
=PERCENTILE(A1:A4,.7) 51.4

PI

The PI returns a the mathematical constant π, also known as Pi. Pi is the ratio of a circle's circumference to its diameter.

Syntax

PI()

Return Value: Number

Example Usage

Input Result
=PI() 3.141593
=DEGREES(PI()*.5) 90

POWER

The POWER formula returns a number raised to a power.

Syntax

POWER( base, exponent )
  • base - number to raise to the exponent power
  • exponent - exponent to be applied to the base

Return Value: Number

Example Usage

Input Result
=POWER(2,6) 64
=POWER(2,8) 256

PROPER

The PROPER formula converts text into lower case with the first character between words in upper case.

Syntax

PROPER( text )
  • text - text to proper

Return Value: text

Example Usage

Input Result
=PROPER("row64") Row64
=PROPER("row64 gpu compute") Row64 Gpu Compute

PYTHONTIME

The PYTHONTIME formula converts Excel Spreadsheet time to Python Time.

Python time is nanoseconds from the UNIX Epoch January 1, 1970.

In Excel time, the integer part represents days since January 1, 1900, and the decimal part represents time.

Syntax

PYTHONTIME( excel_time )
  • excel_time - number in excel time

Return Value: Number

Example Usage Assumes cell has been formated as a date / time

Input Result Reference Date
=PYTHONTIME(36526) 946684800000000000 1/1/2000
=PYTHONTIME(DATEVALUE("1970-1-1")) 0 Unix Epoch

QUARTER

The QUARTER formula returns the financial quarter of a date. The start month of the financial year can also be optionally set.

Syntax

PYTHONTIME( date, [month_offset] )
  • date - date number
  • month_offset - (optional) fiscal offset in months

Return Value: Number

Example Usage

Input Result Note
=QUARTER(DATEVALUE("8/1/2020")) 3 Financial Year Starts Jan
=QUARTER(DATEVALUE("8/1/2020"),6) 1 Financial Year offset of 6 months

QUOTIENT

The QUOTIENT formula returns the result of one number divided by another without any remainder.

Syntax

QUOTIENT( number, divisor )
  • number - numeric value
  • divisor - number to divide by

Return Value: Number

Example Usage

Input Result
=QUOTIENT(5,2) 2
=QUOTIENT(64,7) 9

RADIANS

The RADIANS formula converts from degrees to radians.

Syntax

RADIANS( number)
  • number - angle in degrees

Return Value: Number

Example Usage

Input Result
=RADIANS(180) 3.141593
=RADIANS(1) 0.017453

RAND

The RAND formula returns a random number between 0 and 1.

Syntax

RAND()

Return Value: Number

Example Usage

Input Result
=RAND() 0.902012
=RAND() * 64 24.802013

RANK

The RANK formula returns the rank of a number in a list when ordered by size.

Syntax

RANK( value, range, [order])
  • value - value to get the rank for
  • range - range to find the rank in
  • order - (optional) default 0 = descending order, 1 = ascending order

Return Value: text or number

Example Usage

Data

Score
1 64
2 2
3 15
4 6.4

Calculations

Input Result
=RANK(15,A1:A4) 2
=RANK(64,A1:A4,1) 4

RE_EXTRACT

The RE_EXTRACT formula returns the first match, given a regular expression pattern. It is a type of Regex Formula.

Syntax

RE_EXTRACT( expression, text )
  • expression - regex expression
  • text- text on which to apply the regex expression

Return Value: Text

Example Usage

Input Result
=RE_EXTRACT("(word1|word2).*","something word2.com") word2.com
=RE_EXTRACT("middle.*?_","find_middle-detail_ignore") middle-detail_

RE_EXTRACT_ALL

The RE_EXTRACT_ALL formula extracts all matches and joins them together, given a regular expression pattern. It is a type of Regex Formula.

Syntax

RE_EXTRACT_ALL( expression, text )
  • expression - regex expression
  • text- text on which to apply the regex expression

Return Value: Text

Example Usage

Input Result
=RE_EXTRACT_ALL("\d","aaa2bb33bcc4") 2334
=RE_EXTRACT_ALL("[a-zA-Z]","aaa2bb33bcc4") aaabbbcc

RE_EXTRACT_NTH

The RE_EXTRACT_NTH formula returns the Nth match of regex match groups, given a regular expression pattern. Index counting starts at 1, where 1 is the full match set. 2 is the first set in parentheses, 3 is the second, etc. It is a type of Regex Formula.

RE_EXTRACT_TOKEN_NTH might be a good alternative for certain use cases, which uses a token cycling approach where a single regex is applied over and over, gathered in a list, and returns the Nth item.

Syntax

RE_EXTRACT_NTH( expression, text, number )
  • expression - regex expression
  • text- text on which to apply the regex expression
  • number- index of regex match group (1-based)

Return Value: Text

Example Usage

Input Result
=RE_EXTRACT_NTH("(.)/(.)/(.*)/","Obj1/Obj2/Obj3/", 4) Obj3
=RE_EXTRACT_NTH("([A-Z]+).*?([A-Z]+)","--DROP--HIDDEN--DROP", 3) HIDDEN

RE_EXTRACT_TOKEN_NTH

The RE_EXTRACT_TOKEN_NTH formula returns the Nth match of regex token cycling, it is a type of Token Formula.

It uses a token cycling approach, where a single regex is applied over and over, collecting the first match of each cycle into a list. Then, from this list, you grab the Nth item. Index counting starts at 1, where 1 is the first cycle captured, 2 is the second, etc.

RE_EXTRACT_NTH might be a good alternative for certain use cases, which instead gives the NTH item in regex match groups.

Syntax

RE_EXTRACT_TOKEN_NTH( expression, text, number )
  • expression - regex expression
  • text- text on which to apply the regex expression
  • number- index of regex token cycle (1-based)

Return Value: Text

Example Usage

Input Result
=RE_EXTRACT_TOKEN_NTH("\d+","D1/D21/D31/", 2) 21
=RE_EXTRACT_TOKEN_NTH("[a-zA-Z0-9]+","col1,col2,col3,col4", 3) col3

RE_FUZZY_EXTRACT

The RE_FUZZY_EXTRACT formula runs an approximate regex search and returns any matching substring. It is a type of Fuzzy Regex Search.

Syntax

RE_FUZZY_EXTRACT( expression, text )
  • expression - regex expression
  • text- text on which to apply the regex expression

Return Value: Text

Example Usage

Input Result
=RE_FUZZY_EXTRACT("(abc)|(cba)","000a0bc000") a0bc
=RE_FUZZY_EXTRACT("(foo)|(bar)|(baz)","-=~bah=~") bah

RE_FUZZY_MATCH

The RE_FUZZY_MATCH formula runs an approximate regex search and returns TRUE if a match is found. It is a type of Fuzzy Regex Search.

Syntax

RE_FUZZY_MATCH( expression, text )
  • expression - regex expression
  • text- text on which to apply the regex expression

Return Value: TRUE or FALSE

Example Usage

Input Result
=RE_FUZZY_MATCH("(aaa|abc)","000a0bc000") TRUE
=RE_FUZZY_MATCH("(foo)|(bar)|(baz)","-=~bah=~") TRUE

RE_FUZZY_REPLACE

The RE_FUZZY_REPLACE formula runs an approximate regex search and returns TRUE if a match is found. It is a type of Fuzzy Regex Search.

Syntax

RE_FUZZY_REPLACE( expression, text, replacement )
  • expression - regex expression
  • text- text on which to apply the regex expression
  • replacement- text used to replace the pattern

Return Value: Text

Example Usage

Input Result
=RE_FUZZY_REPLACE("(aaa|abc)","000a0bc000","####") 000####000
=RE_FUZZY_REPLACE("(foo)|(bar)|(baz)","-=bah=~","HELLO") -=HELLO=~

RE_MATCH

The RE_MATCH returns TRUE is a match to a regex expression is found. It is a type of Regex Formula.

Syntax

RE_MATCH( expression, text )
  • expression - regex expression
  • text- text on which to apply the regex expression

Return Value: TRUE or FALSE

Example Usage

Input Result
=RE_MATCH(".*word","testword.jpg") TRUE
=RE_MATCH("(foo)|(bar)","-=jar=-") FALSE

RE_REPLACE

The RE_REPLACE replaces the first regex pattern match with a substitute string. It is a type of Regex Formula.

Syntax

RE_REPLACE( expression, text, replacement )
  • expression - regex expression
  • text- text on which to apply the regex expression
  • replacement- text used to replace the pattern

Example Usage

Input Result
=RE_REPLACE("company1","hello from company1", "company2") hello from company2
=RE_REPLACE("X.*?1","Check out X3453451", "R64") Check out R64

RE_REPLACE_ALL

The RE_REPLACE_ALL replaces the first regex pattern match with a substitute string. It is a type of Regex Formula.

Syntax

RE_REPLACE_ALL( expression, text, replacement )
  • expression - regex expression
  • text- text on which to apply the regex expression
  • replacement- text used to replace the pattern

Example Usage

Input Result
=RE_REPLACE_ALL("i.*?,","item1,item2,item1,item3", "--> ") --> --> --> item3
=RE_REPLACE_ALL("[0-9,]+","34234.csv,23424.tsv,", "|") |.csv|.tsv|

RE_REPLACE_NTH

The RE_REPLACE_NTH replaces the Nth item matched with a regex pattern match with a substitute string. It is a type of Regex Formula.

RE_REPLACE_TOKEN_NTH might be a good alternative for certain use cases, which instead gives the NTH item in token cycling with regex.

Syntax

RE_REPLACE_NTH( expression, text, replacement, number )
  • expression - regex expression
  • text- text on which to apply the regex expression
  • replacement- text used to replace the pattern
  • number- index of regex match (1-based)

Example Usage

Input Result
=RE_REPLACE_NTH("i.*?1","item1,item1,item1,item1", "###",3) item1,item1,###,item1
=RE_REPLACE_NTH("[0-9:]+","2014-08-17T14:07:30+0521", "1:00:00",4) 2014-08-17T1:00:00+0521

RE_REPLACE_TOKEN_NTH

The RE_REPLACE_TOKEN_NTH replaces Nth item matched with regex token cycling with a substitute string. It is a type of Token Formula.

RE_REPLACE_NTH might be a good alternative for certain use cases, which instead gives the NTH item using only regex.

Syntax

RE_REPLACE_TOKEN_NTH( expression, text, replacement, number)
  • expression - regex expression
  • text- text on which to apply the regex expression
  • replacement- text used to replace the pattern
  • number- index of regex match (1-based)

Example Usage

Input Result
=RE_REPLACE_TOKEN_NTH("\d+","__002__123__987__", "###",2) __002__###__987__
---

RE_JOIN_TOKENS

The RE_JOIN_TOKENS splits tokens and then groups them together with a separator. It is a type of Token Formula.

Syntax

RE_JOIN_TOKENS( expression, text, replacement )
  • expression - regex expression
  • text- text on which to apply the regex expression
  • replacement- text used to join between matches

Example Usage

Input Result
=RE_JOIN_TOKENS("\d+","__012--345==678##","|") 012|345|678
=RE_JOIN_TOKENS("[a-zA-Z0-9]+","a12,,b23,,,c1","-") a12-b23-c1

RE_JOIN_SPLITTERS

The RE_JOIN_SPLITTERS splits tokens and then captures the splitters into a list. This list is joined by a seperator. The items you are not collecting are joined together.

It is a type of Token Formula. This can be useful for interleaving operations where you capture both split elements and delimiters in different passes.

Syntax

RE_JOIN_SPLITTERS( expression, text, replacement )
  • expression - regex expression
  • text- text on which to apply the regex expression
  • replacement- text used to join between splitters

Example Usage

Input Result
= RE_JOIN_SPLITTERS("\d+","__012--345==678##","|") __|--|==|##
=RE_JOIN_SPLITTERS("[-TZ+:]","2014-08-17T14:07:30+0521","|") 2014|08|17|14|07|30|0521

REP_EXTRACT

The REP_EXTRACT formula returns the first match, given a POSIX regular expression pattern. It is a type of POSIX Regex Formula.

Syntax

REP_EXTRACT( expression, text )
  • expression - POSIX regex expression
  • text- text on which to apply the regex expression

Return Value: Text

Example Usage

Input Result
=REP_EXTRACT("([[:alpha:]]*)","123..bob4@gmail.com") bob
=REP_EXTRACT("([hc]at*)","dog & cat") cat

REP_MATCH

The REP_MATCH returns TRUE is a match to a regex expression is found. It is a type of POSIX Regex Formula.

Syntax

REP_MATCH( expression, text )
  • expression - POSIX regex expression
  • text - text on which to apply the regex expression

Return Value: TRUE or FALSE

Example Usage

Input Result
=REP_MATCH(".*t","testword") TRUE
=REP_MATCH("a[[:digit:]]","a0b") TRUE

REP_REPLACE

The REP_REPLACE replaces the first regex pattern match with a substitute string. It is a type of POSIX Regex Formula.

Syntax

REP_REPLACE( expression, text, replacement )
  • expression - POSIX regex expression
  • text - text on which to apply the regex expression
  • replacement - text used to replace the pattern

Example Usage

Input Result
=REP_REPLACE("a[[:digit:]]","this a0 that", "###") this ### that
=REP_REPLACE("[.].*","test.txt", ".py") test.py

REP_REPLACE_ALL

The REP_REPLACE_ALL replaces the first regex pattern match with a substitute string. It is a type of POSIX Regex Formula.

Syntax

REP_REPLACE_ALL( expression, text, replacement )
  • expression - POSIX regex expression
  • text - text on which to apply the regex expression
  • replacement - text used to replace the pattern

Example Usage

Input Result
=REP_REPLACE_ALL("[[:digit:]]","15ab453c4d", "*") **ab***c*d
=REP_REPLACE_ALL("<.*>","learn \about\ Row64", "AWESOME") learn AWESOME Row64

REP_REPLACE_NTH

The REP_REPLACE_NTH replaces the Nth item matched with a regex pattern match with a substitute string. It is a type of POSIX Regex Formula.

RE_REPLACE_TOKEN_NTH might be a good alternative for certain use cases, which instead gives the NTH item in token cycling with regex.

Syntax

RE_REPLACE_NTH( expression, text, replacement, number )
  • expression - POSIX regex expression
  • text - text on which to apply the regex expression
  • replacement - text used to replace the pattern
  • number - index of regex match (1-based)

Example Usage

Input Result
=REP_REPLACE_NTH("[[:digit:]]","a1,b1,c1", "###",2) a1,b###,c1
=REP_REPLACE_NTH("[[:alnum:]]*>","\\\", "tag100>",2) \\\

REPLACE

REPLACE returns the number of blank values in a range or list of values.

Syntax

"old_text", "start_num", "num_chars", "new_text"

REPLACE( text, start_num, num_chars, insert_text )
  • text - text to modify
  • start_num - position where the replacement will begin (starting from 1)
  • num_chars - number of characters in the text to be replaced
  • insert_text - text which to be inserted

Return Value: Number

Example Usage

Input Result
=REPLACE("Geospatial R64 dashboards",12,3,"Row64") Geospatial Row64 dashboards
=REPLACE("The way of the future",5,3,"dashboard") The dashboard of the future

REPT

REPT returns text repeated a set number of times.

Syntax

REPT( text, nb_repeats )
  • text - text to repeat
  • nb_repeats - number of times the text is repeated

Return Value: Text

Example Usage

Input Result
=REPT("R64|",3) R64|R64|R64|
=REPT("<(*)>",5) <(*)><(*)><(*)><(*)><(*)>

The RIGHT formula takes text and returns a substring, starting on the right side, and gathering a given number of characters backwards.

Syntax

RIGHT( text, [nb_characters] )
  • text - text to modify
  • nb_characters - (optional) number of characters backwards from the right side

Return Value: text

Example Usage

Input Result
=RIGHT("Row64",2) 64
=RIGHT("Fast Dashboard",9) Dashboard

ROUND

The ROUND formula rounds to the nearest integer specifying the number of decimal places.

Syntax

ROUND( number, nb_decimals )
  • number - amount
  • nb_decimals - number of decimal places

Return Value: Number

Example Usage

Input Result
=ROUND(2.4123, 1) 2.4
=ROUND(2.611111, 3) 2.611

ROW

ROW returns the row index of the formula location. It is 1-based: A1 returns 1, A2 returns 2, etc.

Syntax

ROW()

Return Value: Index of row

Example Usage

In this example, assume the formula was entered in cell B10.

Input Result
=ROW() 10

SEARCH returns the position of the first instance of a search term, from within a string of text. It ignores case.

Syntax

SEARCH( search_text, within_text )
  • search_text - search term
  • within_text - text to search with

Return Value: Number

Example Usage

Input Result
=SEARCH("dash","gpu dashboards") 5
=SEARCH("visual","Real-Time Visual Intelligence") 11

SECOND

The SECOND formula returns the second of a date time

Syntax

SECOND( date_number )
  • date_number - date time to extract the second from

Return Value: Second Number

Example Usage

Input Result
=SECOND(DATEVALUE("8/18/2018 1:06:14 PM")) 14
=SECOND(DATEVALUE("8/18/2018 5:27:31AM")) 31

SENTIMENT

The SENTIMENT formula analyses text and returns a polarity (positive/negative) score. The score range is from -1 to 1, with -1 being very negative, and 1 being very positive. It is a type of Sentiment Formula.

Syntax

SENTIMENT( text )
  • text - text to evaluate sentiment

Return Value: Polarity Score Number

Example Usage

Input Result
=SENTIMENT("Row64 is AWESOME!") 0.729026
=SENTIMENT("I adore Row64") 0.55737

SIGN

The SIGN formula indicates if a number is positive or negative. SIGN returns:

  • -1 if a number is negative
  • 1 if a number is positive
  • 0 if the input is 0

Syntax

SIGN( number )
  • number - input number

Return Value: Number

Example Usage

Input Result
=SIGN(64) 1
=SIGN(-7) -1

SIN

The SIN formula returns the sin of a number.

Syntax

SIN( number )
  • number - input number

Return Value: Number

Example Usage

Input Result
=SIN(PI()*.5) 1
=SIN(PI()*.1) 0.309017

SINH

The SINH formula returns the hyperbolic sin of a number.

Syntax

SINH( number )
  • number - input number

Return Value: Number

Example Usage

Input Result
=SINH(PI()) 11.548739
=SINH(PI()*.1) 0.319353

SQRT

The SQRT formula returns the square root of a number.

Syntax

SQRT( number )
  • number - input number

Return Value: Number

Example Usage

Input Result
=SQRT(64) 8
=SQRT(2) 1.414214

SUBSTITUTE

The SUBSTITUTE formula replaces a search term with new text in a string.

Syntax

SUBSTITUTE( text, search_term, new_text, [occurrence_number] )
  • text - text to search
  • search_term - term to search for
  • new_text - text to replace the search_term with
  • occurrence_number - (optional) by default, all occurances are replaced. If an occurrence number is set, then only that occurance is set.

Return Value: Number

Example Usage

Input Result
=SUBSTITUTE("January 10, 2010",10,7,1) January 7, 2010
=SUBSTITUTE("Exploring Billions of Records","Billions","A Massive Scale") Exploring A Massive Scale of Records

SUM

The SUM formula returns the sum of a list of numbers.

Syntax

SUM( value1, [value2, ...] )
  • value1 - number for evaluation
  • value2, ... (optional) - additional number(s) for evaluation

Return Value: number

Example Usage

Input Result
=SUM(6,64,4,64) 138
=SUM(0,1,0,1,0,1,1,1) 5

SUMIF

The SUMIF formula returns the sum of the input values if the given criteria is satisfied.

Syntax

SUMIF( range, criteria )
  • range - range of values on which to test the criteria
  • criteria - criteria or condition to check for. This is a quoted expression that begins with a comparison operator: =, <>, <, <=, >, >=.
    • Examples: "=abc" or "<100"
    • If the criteria expression does not begin with an operator, then it is assumed to be =.

Return Value: Number

Example Usage

Data

A B
1 2 4
2 row64 3
3 9 2
4 6 7

Calculations

Input Result
=SUMIF(B1:B4,">3") 11
=SUMIF(A1:A4,"<>row64") 17

Notes

  • NULL and non-numeric values are ignored.

SUMIFS

The SUMIFS formula returns the sum of the input values depending on multiple criteria.

Syntax

SUMIFS( range, range1, criteria1, range2, criteria2, ... )
  • range - range to sum
  • range1 - range to test criteria
  • criteria1 - criteria to check on range1. This is a quoted expression that begins with a comparison operators: =, <>, <, <=, >, >=.
    • Examples: "=abc" or "<100"
    • If the criteria expression does not begin with an operator, then it is assumed to be =.
  • range2 - range to test criteria2
  • criteria2 - criteria to check on range2

Return Value: Number Example Usage

Data

A B
1 2 4
2 row64 3
3 9 2
4 6 7

Calculations

Input Result
=SUMIFS(A1:A4,B1:B4,"<5",A1:A4,"<>row64") 11
=SUMIFS(B1:B4,B1:B4,">3",A1:A4,">4") 7

Notes

  • NULL and non-numeric values in the average range are ignored.

T

The T formula returns the text referred to by value. If the value is not text, it returns empty text.

Syntax

T( value )
  • value - value to return as text

Return Value: Number

Example Usage

Input Result
=T("64") 64
=T(64)

TAN

The TAN formula returns the tangent of a number.

Syntax

TAN( number )
  • number - input number in radians

Return Value: Number

Example Usage

Input Result
=TAN(1) 1.557408
=TAN(64) 2.34786

TANH

The TANH formula returns the hyberbolic tangent of a number.

Syntax

TANH( number )
  • number - input number

Return Value: Number

Example Usage

Input Result
=TANH(1) 0.761594
=TANH(64) 1

TEXTJOIN

The TEXTJOIN formula conjoins text.

Syntax

TEXTJOIN( delimiter, ignore_empty, [text1, ...] )
  • delimiter - text to put between pieces
  • ignore_empty - TRUE to ignore empty, FALSE keeps empty
  • text1, ... - text to join

Return Value: number

Example Usage

Input Result
=TEXTJOIN(" > ",TRUE,"real-time","data-driven","decisions") real-time > data-driven > decisions
=TEXTJOIN(" | ",TRUE,"Ubuntu","RedHat","Windows","Arm","Mac") Ubuntu | RedHat | Windows | Arm | Mac

TODAY

The TODAY formula returns a datetime of the current day.

Syntax

TODAY()

Return Value: Datetime Number

Example Usage

Assumes cell has been formated as a date

Input Result
=TODAY() 9/22/2025
=TODAY() + 20 10/12/2025

TRIM

The TRIM formula removes any white space at the front and back of text.

Syntax

TRIM( text )
  • text - text to trim

Return Value: Text

Example Usage

Input Result
=TRIM("     Row64   ") Row64
=TRIM("  Row  ") & TRIM("  64  ") Row64

TRUNC

The TRUNC formula sets the number decimal places to a set number of digits, without rounding.

Syntax

TRUNC( number, nb_decimals )
  • number - amount
  • nb_decimals - number of decimal places

Return Value: Number

Example Usage

Input Result
=TRUNC(2.4923, 1) 2.4
=TRUNC(2.69876, 3) 2.698

UPPER

The UPPER formula converts text into upper case.

Syntax

UPPER( text )
  • text - text to upper

Return Value: text

Example Usage

Input Result
=UPPER("Row64") ROW64
=UPPER("AwEsOmE") AWESOME

VALUE

The VALUE formula converts text into a number or datetime number.

Syntax

VALUE( text )
  • text - text to upper

Return Value: text

Example Usage

Assumes cell has been formated as a date / time

Input Result
=VALUE("64") / 4 16
=VALUE("7/20/2025") + 10 7/30/2025

VLOOKUP

The VLOOKUP formula vertical searches for a key value in the first column of a 2D range. It returns a matching value on a column to the right. This number of columns over to the right is specified by an index value.

Syntax

VLOOKUP( search_value, range, index, [exact_match] )
  • search_value - value to search for
  • range - 2D range to search, lookup value should be in first column
  • index - index to in 2D range for return value (must be positive integer)
  • exact_match - (optional):
    • FALSE (default) = exact match
    • TRUE = approximate match (sort your search column in ascending order)

Return Value: text or number

Example Usage

Data

Level Product
1 100 Row64 Plus
2 200 Row64 Pro
3 300 Row64 Unlimited
4 400 Row64 Light Speed

Calculations

Input Result
=VLOOKUP(300,A1:B4,2) Row64 Unlimited
=VLOOKUP(400,A1:B4,2) Row64 Light Speed

WEEKDAY

The WEEKDAY formula returns the week day of a date time where 1 = Sunday and 7 = Saturday

Syntax

WEEKDAY( date_number )
  • date_number - date time to extract the hour from

Return Value: WEEKDAY Number

Example Usage

Input Result
=WEEKDAY(DATEVALUE("8/18/2018")) 7
=WEEKDAY(DATEVALUE("1/30/2018")) 3

XOR

The XOR formula returns TRUE if any of the expressions in a list is FALSE. Otherwise, TRUE.

Syntax

XOR( expression1, [expression2 ...] )
  • expression1 - expression or cell reference
  • expression2 ... - (optional) expression or cell reference

Return Value: TRUE or FALSE

Example Usage

Input Result
=XOR(FALSE, FALSE, TRUE) TRUE
=XOR(1=2,3=3,4=4) FALSE

YEAR

The YEAR formula returns the year of a date time.

Syntax

YEAR( date_number )
  • date_number - date time to extract the year from

Return Value: YEAR Number

Example Usage

Input Result
=YEAR(DATEVALUE("8/18/2018")) 2018
=2089 - YEAR(DATEVALUE("1/30/2025")) 64