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!
Last updated