Formula Reference

Mito supports 75+ of the most common Excel spreadsheet formulas, implemented in Python.

Formula Reference

Missing a formula, or what to add a formula of your own design? Check out bring-your-own spreadsheet functions, which allow you to add additional functionality directly into the mitosheet.

ABS

Returns the absolute value of the passed number or series.

Examples

• ABS(-1.3)

• ABS(A)

Syntax

ABS(value)

Syntax Elements

• value: The value or series to take the absolute value of.

AND

Returns True if all of the provided arguments are True, and False if any of the provided arguments are False.

Examples

• AND(True, False)

• AND(Nums > 100, Nums < 200)

• AND(Pay > 10, Pay < 20, Status == 'active')

Syntax

AND(boolean_condition1, [boolean_condition2, ...])

Syntax Elements

• boolean_condition1: An expression or series that returns True or False values. See IF documentation for a list of conditons.

• boolean_condition2 ... [OPTIONAL]: An expression or series that returns True or False values. See IF documentation for a list of conditons.

AVG

Returns the numerical mean value of the passed numbers and series.

Examples

• AVG(1, 2)

• AVG(A, B)

• AVG(A, 2)

Syntax

AVG(value1, [value2, ...])

Syntax Elements

• value1: The first number or series to consider when calculating the average.

• value2, ... [OPTIONAL]: Additional numbers or series to consider when calculating the average.

BOOL

Converts the passed arguments to boolean values, either True or False. For numberic values, 0 converts to False while all other values convert to True.

Examples

• BOOL(Amount_Payed)

• AND(BOOL(Amount_Payed), Is_Paying)

Syntax

BOOL(series)

Syntax Elements

• series: An series to convert to boolean values, either True or False.

CLEAN

Returns the text with the non-printable ASCII characters removed.

Examples

• CLEAN(A)

Syntax

CLEAN(string)

Syntax Elements

• string: The string or series whose non-printable characters are to be removed.

CONCAT

Returns the passed strings and series appended together.

Examples

• CONCAT('Bite', 'the bullet')

• CONCAT(A, B)

Syntax

CONCAT(string1, [string2, ...])

Syntax Elements

• string1: The first string or series.

• string2, ... [OPTIONAL]: Additional strings or series to append in sequence.

CORR

Computes the correlation between two series, excluding missing values.

Examples

• =CORR(A, B)

• =CORR(B, A)

Syntax

CORR(series_one, series_two)

Syntax Elements

• series_one: The number series to convert to calculate the correlation.

• series_two: The number series to convert to calculate the correlation.

DATEVALUE

Converts a given string to a date series.

Examples

• DATEVALUE(date_column)

• DATEVALUE('2012-12-22')

Syntax

DATEVALUE(date_string)

Syntax Elements

• date_string: The date string to turn into a date object.

DAY

Returns the day of the month that a specific date falls on, as a number.

Examples

• DAY(date_column)

• DAY('2012-12-22')

Syntax

DAY(date)

Syntax Elements

• date: The date or date series to get the day of.

ENDOFBUSINESSMONTH

Given a date, returns the end of the buisness month. E.g. the last weekday.

Examples

• ENDOFBUSINESSMONTH(date_column)

• ENDOFBUSINESSMONTH('2012-12-22')

Syntax

ENDOFBUSINESSMONTH(date)

Syntax Elements

• date: The date or date series to get the end of the business month of.

ENDOFMONTH

Given a date, returns the end of the month, as a date. E.g. input of 12-22-1997 will return 12-31-1997.

Examples

• ENDOFMONTH(date_column)

• ENDOFMONTH('2012-12-22')

Syntax

ENDOFMONTH(date)

Syntax Elements

• date: The date or date series to get the last day of the month of.

EXP

Returns e, the base of the natural logarithm, raised to the power of passed series.

Examples

• =EXP(data)

• =EXP(A)

Syntax

EXP(series)

Syntax Elements

• series: The series to raise e to.

FILLNAN

Replaces the NaN values in the series with the replacement value.

Examples

• FILLNAN(A, 10)

• FILLNAN(A, 'replacement')

Syntax

FILLNAN(series, replacement)

Syntax Elements

• series: The series to replace the NaN values in.

• replacement: A string, number, or date to replace the NaNs with.

FIND

Returns the position at which a string is first found within text, case-sensitive. Returns 0 if not found.

Examples

• FIND(A, 'Jack')

• FIND('Ben has a friend Jack', 'Jack')

Syntax

FIND(text_to_search, search_for)

Syntax Elements

• text_to_search: The text or series to search for the first occurrence of search_for.

• search_for: The string to look for within text_to_search.

FLOAT

Converts a string series to a float series. Any values that fail to convert will return NaN.

Examples

• =FLOAT(Prices_string)

• =FLOAT('123.123')

Syntax

FLOAT(string_series)

Syntax Elements

• string_series: The series or string to convert to a float.

GETPREVIOUSVALUE

Returns the value from series that meets the condition.

Examples

• GETPREVIOUSVALUE(Max_Balances, Max_Balances > 0)

Syntax

GETPREVIOUSVALUE(series, condition)

Syntax Elements

• series: The series to get the previous value from.

• condition: When condition is True, a new previous value is set, and carried forward until the condition is True again.

HOUR

Returns the hour component of a specific date, as a number.

Examples

• HOUR(date_column)

• HOUR('2012-12-22 09:45:00')

Syntax

HOUR(date)

Syntax Elements

• date: The date or date series to get the hour of.

IF

Returns one value if the condition is True. Returns the other value if the conditon is False.

Examples

• IF(Status == 'success', 1, 0)

• IF(Nums > 100, 100, Nums)

• IF(AND(Grade >= .6, Status == 'active'), 'pass', 'fail')

Syntax

IF(boolean_condition, value_if_true, value_if_false)

Syntax Elements

• boolean_condition: An expression or series that returns True or False values. Valid conditions for comparison include ==, !=, >, <, >=, <=.

• value_if_true: The value the function returns if condition is True.

• value_if_false: The value the function returns if condition is False.

INT

Converts a string series to a int series. Any values that fail to convert will return 0.

Examples

• =INT(Prices_string)

• =INT('123')

Syntax

INT(string_series)

Syntax Elements

• string_series: The series or string to convert to a int.

KURT

Computes the unbiased kurtosis, a measure of tailedness, of a series, excluding missing values.

Examples

• =KURT(A)

• =KURT(A * B)

Syntax

KURT(series)

Syntax Elements

• series: The series to calculate the unbiased kurtosis of.

LEFT

Returns a substring from the beginning of a specified string.

Examples

• LEFT(A, 2)

• LEFT('The first character!')

Syntax

LEFT(string, [number_of_characters])

Syntax Elements

• string: The string or series from which the left portion will be returned.

• number_of_characters [OPTIONAL, 1 by default]: The number of characters to return from the start of string.

LEN

Returns the length of a string.

Examples

• LEN(A)

• LEN('This is 21 characters')

Syntax

LEN(string)

Syntax Elements

• string: The string or series whose length will be returned.

LOG

Calculates the logarithm of the passed series with an optional base.

Examples

• LOG(e) = 1

• LOG(100, 10) = 2

Syntax

LOG(series, [base])

Syntax Elements

• series: The series to take the logarithm of.

• base [OPTIONAL]: The base of the logarithm to use. Defaults to the natural logarithm if no base is passed.

LOWER

Converts a given string to lowercase.

Examples

• =LOWER('ABC')

• =LOWER(A)

• =LOWER('Nate Rush')

Syntax

LOWER(string)

Syntax Elements

• string: The string or series to convert to lowercase.

MAX

Returns the maximum value among the passed arguments.

Examples

• MAX(10, 11)

• MAX(Old_Data, New_Data)

Syntax

MAX(value1, [value2, ...])

Syntax Elements

• value1: The first number or column to consider for the maximum value.

• value2, ... [OPTIONAL]: Additional numbers or columns to compute the maximum value from.

MID

Returns a segment of a string.

Examples

• MID(A, 2, 2)

• MID('Some middle characters!', 3, 4)

Syntax

MID(string, starting_at, extract_length)

Syntax Elements

• string: The string or series to extract the segment from.

• starting_at: The index from the left of string from which to begin extracting.

• extract_length: The length of the segment to extract.

MIN

Returns the minimum value among the passed arguments.

Examples

• MIN(10, 11)

• MIN(Old_Data, New_Data)

Syntax

MIN(value1, [value2, ...])

Syntax Elements

• value1: The first number or column to consider for the minumum value.

• value2, ... [OPTIONAL]: Additional numbers or columns to compute the minumum value from.

MINUTE

Returns the minute component of a specific date, as a number.

Examples

• MINUTE(date_column)

• MINUTE('2012-12-22 09:45:00')

Syntax

MINUTE(date)

Syntax Elements

• date: The date or date series to get the minute of.

MONTH

Returns the month that a specific date falls in, as a number.

Examples

• MONTH(date_column)

• MONTH('2012-12-22')

Syntax

MONTH(date)

Syntax Elements

• date: The date or date series to get the month of.

MULTIPLY

Returns the product of two numbers.

Examples

• MULTIPLY(2,3)

• MULTIPLY(A,3)

Syntax

MULTIPLY(factor1, [factor2, ...])

Syntax Elements

• factor1: The first number to multiply.

• factor2, ... [OPTIONAL]: Additional numbers or series to multiply.

OR

Returns True if any of the provided arguments are True, and False if all of the provided arguments are False.

Examples

• OR(True, False)

• OR(Status == 'success', Status == 'pass', Status == 'passed')

Syntax

OR(boolean_condition1, [boolean_condition2, ...])

Syntax Elements

• boolean_condition1: An expression or series that returns True or False values. See IF documentation for a list of conditons.

• boolean_condition2 ... [OPTIONAL]: An expression or series that returns True or False values. See IF documentation for a list of conditons.

POWER

The POWER function can be used to raise a number to a given power.

Examples

• POWER(4, 1/2)

• POWER(Dose, 2)

Syntax

POWER(value, exponent)

Syntax Elements

• value: Number to raise to a power.

• exponent: The number to raise value to.

PROPER

Capitalizes the first letter of each word in a specified string.

Examples

• =PROPER('nate nush')

• =PROPER(A)

Syntax

PROPER(string)

Syntax Elements

• string: The value or series to convert to convert to proper case.

QUARTER

Returns the quarter (1-4) that a specific date falls in, as a number.

Examples

• QUARTER(date_column)

• QUARTER('2012-12-22')

Syntax

QUARTER(date)

Syntax Elements

• date: The date or date series to get the quarter of.

RIGHT

Returns a substring from the beginning of a specified string.

Examples

• RIGHT(A, 2)

• RIGHT('The last character!')

Syntax

RIGHT(string, [number_of_characters])

Syntax Elements

• string: The string or series from which the right portion will be returned.

• number_of_characters [OPTIONAL, 1 by default]: The number of characters to return from the end of string.

ROUND

Rounds a number to a given number of decimals.

Examples

• ROUND(1.3)

• ROUND(A, 2)

Syntax

ROUND(value, [decimals])

Syntax Elements

• value: The value or series to round.

• decimals: The number of decimals to round to. Default is 0.

SECOND

Returns the seconds component of a specific date, as a number.

Examples

• SECOND(date_column)

• SECOND('2012-12-22 09:23:05')

Syntax

SECOND(date)

Syntax Elements

• date: The date or date series to get the seconds of.

SKEW

Computes the skew of a series, excluding missing values.

Examples

• =SKEW(A)

• =SKEW(A * B)

Syntax

SKEW(series)

Syntax Elements

• series: The series to calculate the skew of.

STARTOFBUSINESSMONTH

Given a date, returns the most recent start of the business month, as a state. E.g. the first weekday.

Examples

• STARTOFBUSINESSMONTH(date_column)

• STARTOFBUSINESSMONTH('2012-12-22 09:23:05')

Syntax

STARTOFBUSINESSMONTH(date)

Syntax Elements

• date: The date or date series to get the most recent beginning of month business day of.

STARTOFMONTH

Given a date, returns the start of the month, as a date. E.g. input of 12-22-1997 will return 12-1-1997.

Examples

• STARTOFMONTH(date_column)

• STARTOFMONTH('2012-12-22 09:23:05')

Syntax

STARTOFMONTH(date)

Syntax Elements

• date: The date or date series to get the first day of the month of.

STDEV

Computes the standard deviation of a series, excluding missing values.

Examples

• =STDEV(A)

• =STDEV(A * B)

Syntax

STDEV(series)

Syntax Elements

• series: The series to calculate the standard deviation of.

STRIPTIMETODAYS

Returns the date with a seconds, minutes, and hours component of 00:00:00.

Examples

• STRIPTIMETODAYS(date_column)

• STRIPTIMETODAYS('2012-12-22 09:23:05')

Syntax

STRIPTIMETODAYS(date)

Syntax Elements

• date: The date or date series to reset the seconds, minutes, and hours component of.

STRIPTIMETOHOURS

Returns the date with a seconds and minutes component of 00:00.

Examples

• STRIPTIMETOHOURS(date_column)

• STRIPTIMETOHOURS('2012-12-22 09:23:05')

Syntax

STRIPTIMETOHOURS(date)

Syntax Elements

• date: The date or date series to reset the seconds and minutes component of.

STRIPTIMETOMINUTES

Returns the date with a seconds component of 00.

Examples

• STRIPTIMETOMINUTES(date_column)

• STRIPTIMETOMINUTES('2012-12-22 09:23:05')

Syntax

STRIPTIMETOMINUTES(date)

Syntax Elements

• date: The date or date series to reset the seconds component of.

STRIPTIMETOMONTHS

Returns the date adjusted to the start of the month.

Examples

• STRIPTIMETOMONTHS(date_column)

• STRIPTIMETOMONTHS('2012-12-22 09:23:05')

Syntax

STRIPTIMETOMONTHS(date)

Syntax Elements

• date: The date or date series to reset the seconds, minutes, hours, and days of.

STRIPTIMETOYEARS

Returns the date adjusted to the start of the year.

Examples

• STRIPTIMETOYEARS(date_column)

• STRIPTIMETOYEARS('2012-12-22 09:23:05')

Syntax

STRIPTIMETOYEARS(date)

Syntax Elements

• date: The date or date series to reset the seconds, minutes, hours, days, and month components of.

SUBSTITUTE

Replaces existing text with new text in a string.

Examples

• SUBSTITUTE('Better great than never', 'great', 'late')

• SUBSTITUTE(A, 'dog', 'cat')

Syntax

SUBSTITUTE(text_to_search, search_for, replace_with, [count])

Syntax Elements

• text_to_search: The text within which to search and replace.

• search_for: The string to search for within text_to_search.

• replace_with: The string that will replace search_for.

• count: The number of times to perform the substitute. Default is all.

SUM

Returns the sum of the given numbers and series.

Examples

• SUM(10, 11)

• SUM(A, B, D, F)

• SUM(A, B, D, F)

Syntax

SUM(value1, [value2, ...])

Syntax Elements

• value1: The first number or column to add together.

• value2, ... [OPTIONAL]: Additional numbers or columns to sum.

TEXT

Turns the passed series into a string.

Examples

• =TEXT(Product_Number)

• =TEXT(Start_Date)

Syntax

TEXT(series)

Syntax Elements

• series: The series to convert to a string.

TRIM

Returns a string with the leading and trailing whitespace removed.

Examples

• =TRIM(' ABC')

• =TRIM(' ABC ')

• =TRIM(A)

Syntax

TRIM(string)

Syntax Elements

• string: The value or series to remove the leading and trailing whitespace from.

TYPE

Returns the type of each element of the passed series. Return values are 'number', 'str', 'bool', 'datetime', 'object', or 'NaN'.

Examples

• TYPE(Nums_and_Strings)

• IF(TYPE(Account_Numbers) != 'NaN', Account_Numbers, 0)

Syntax

TYPE(series)

Syntax Elements

• series: The series to get the type of each element of.

UPPER

Converts a given string to uppercase.

Examples

• =UPPER('abc')

• =UPPER(A)

• =UPPER('Nate Rush')

Syntax

UPPER(string)

Syntax Elements

• string: The string or series to convert to uppercase.

VALUE

Converts a string series to a number series. Any values that fail to convert will return an NaN.

Examples

• =VALUE(A)

• =VALUE('123')

Syntax

VALUE(string)

Syntax Elements

• string: The string or series to convert to a number.

VAR

Computes the variance of a series, excluding missing values.

Examples

• =VAR(A)

• =VAR(A - B)

Syntax

VAR(series)

Syntax Elements

• series: The series to calculate the variance of.

VLOOKUP

Looks up a value in a range and returns the value in the same row from a column you specify. Notably, this formula can be used to lookup values between different sheets.

Examples

• VLOOKUP(Names0, Other_Dataframe!Ids:Ages, 1)

• VLOOKUP('John Smith', Names:Ages, 2)

• VLOOKUP(Names0, Ids:Ages, Column Indexes0)

Syntax

VLOOKUP(lookup_value, where, index)

Syntax Elements

• lookup_value: The value to look up.

• where: The range to look up in.

• index: The column index to return.

WEEK

Returns the week (1-52) of a specific date, as a number.

Examples

• WEEK(date_column)

• WEEK('2012-12-22 09:23:05')

Syntax

WEEK(date)

Syntax Elements

• date: The date or date series to get the week of.

WEEKDAY

Returns the day of the week that a specific date falls on. 1-7 corresponds to Monday-Sunday.

Examples

• WEEKDAY(date_column)

• WEEKDAY('2012-12-22')

Syntax

WEEKDAY(date)

Syntax Elements

• date: The date or date series to get the weekday of.

YEAR

Returns the day of the year that a specific date falls in, as a number.

Examples

• YEAR(date_column)

• YEAR('2012-12-22')

Syntax

YEAR(date)

Syntax Elements

• date: The date or date series to get the month of.

Missing a formula? Let us know if your workflow requires formulas that Mito does not yet support. We prioritize adding functionality for active Mito users!