Formula Reference
Mito supports 75+ of the most common Excel spreadsheet formulas, implemented in Python.
Last updated
Was this helpful?
Mito supports 75+ of the most common Excel spreadsheet formulas, implemented in Python.
Last updated
Was this helpful?
Returns the absolute value of the passed number or series.
ABS(-1.3)
ABS(A)
ABS(value)
value: The value or series to take the absolute value of.
Returns True if all of the provided arguments are True, and False if any of the provided arguments are False.
AND(True, False)
AND(Nums > 100, Nums < 200)
AND(Pay > 10, Pay < 20, Status == 'active')
AND(boolean_condition1, [boolean_condition2, ...])
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.
Returns the numerical mean value of the passed numbers and series.
AVG(1, 2)
AVG(A, B)
AVG(A, 2)
AVG(value1, [value2, ...])
value1: The first number or series to consider when calculating the average.
value2, ... [OPTIONAL]: Additional numbers or series to consider when calculating the average.
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.
BOOL(Amount_Payed)
AND(BOOL(Amount_Payed), Is_Paying)
BOOL(series)
series: An series to convert to boolean values, either True or False.
Returns the text with the non-printable ASCII characters removed.
CLEAN(A)
CLEAN(string)
string: The string or series whose non-printable characters are to be removed.
Returns the passed strings and series appended together.
CONCAT('Bite', 'the bullet')
CONCAT(A, B)
CONCAT(string1, [string2, ...])
string1: The first string or series.
string2, ... [OPTIONAL]: Additional strings or series to append in sequence.
Computes the correlation between two series, excluding missing values.
=CORR(A, B)
=CORR(B, A)
CORR(series_one, series_two)
series_one: The number series to convert to calculate the correlation.
series_two: The number series to convert to calculate the correlation.
Converts a given string to a date series.
DATEVALUE(date_column)
DATEVALUE('2012-12-22')
DATEVALUE(date_string)
date_string: The date string to turn into a date object.
Returns the day of the month that a specific date falls on, as a number.
DAY(date_column)
DAY('2012-12-22')
DAY(date)
date: The date or date series to get the day of.
Given a date, returns the end of the buisness month. E.g. the last weekday.
ENDOFBUSINESSMONTH(date_column)
ENDOFBUSINESSMONTH('2012-12-22')
ENDOFBUSINESSMONTH(date)
date: The date or date series to get the end of the business month of.
Given a date, returns the end of the month, as a date. E.g. input of 12-22-1997 will return 12-31-1997.
ENDOFMONTH(date_column)
ENDOFMONTH('2012-12-22')
ENDOFMONTH(date)
date: The date or date series to get the last day of the month of.
Returns e, the base of the natural logarithm, raised to the power of passed series.
=EXP(data)
=EXP(A)
EXP(series)
series: The series to raise e to.
Replaces the NaN values in the series with the replacement value.
FILLNAN(A, 10)
FILLNAN(A, 'replacement')
FILLNAN(series, replacement)
series: The series to replace the NaN values in.
replacement: A string, number, or date to replace the NaNs with.
Returns the position at which a string is first found within text, case-sensitive. Returns 0 if not found.
FIND(A, 'Jack')
FIND('Ben has a friend Jack', 'Jack')
FIND(text_to_search, search_for)
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.
Converts a string series to a float series. Any values that fail to convert will return NaN.
=FLOAT(Prices_string)
=FLOAT('123.123')
FLOAT(string_series)
string_series: The series or string to convert to a float.
Returns the value from series that meets the condition.
GETPREVIOUSVALUE(Max_Balances, Max_Balances > 0)
GETPREVIOUSVALUE(series, condition)
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.
Returns the hour component of a specific date, as a number.
HOUR(date_column)
HOUR('2012-12-22 09:45:00')
HOUR(date)
date: The date or date series to get the hour of.
Returns one value if the condition is True. Returns the other value if the conditon is False.
IF(Status == 'success', 1, 0)
IF(Nums > 100, 100, Nums)
IF(AND(Grade >= .6, Status == 'active'), 'pass', 'fail')
IF(boolean_condition, value_if_true, value_if_false)
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.
Converts a string series to a int series. Any values that fail to convert will return 0.
=INT(Prices_string)
=INT('123')
INT(string_series)
string_series: The series or string to convert to a int.
Computes the unbiased kurtosis, a measure of tailedness, of a series, excluding missing values.
=KURT(A)
=KURT(A * B)
KURT(series)
series: The series to calculate the unbiased kurtosis of.
Returns a substring from the beginning of a specified string.
LEFT(A, 2)
LEFT('The first character!')
LEFT(string, [number_of_characters])
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.
Returns the length of a string.
LEN(A)
LEN('This is 21 characters')
LEN(string)
string: The string or series whose length will be returned.
Calculates the logarithm of the passed series with an optional base.
LOG(e) = 1
LOG(100, 10) = 2
LOG(series, [base])
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.
Converts a given string to lowercase.
=LOWER('ABC')
=LOWER(A)
=LOWER('Nate Rush')
LOWER(string)
string: The string or series to convert to lowercase.
Returns the maximum value among the passed arguments.
MAX(10, 11)
MAX(Old_Data, New_Data)
MAX(value1, [value2, ...])
value1: The first number or column to consider for the maximum value.
value2, ... [OPTIONAL]: Additional numbers or columns to compute the maximum value from.
Returns a segment of a string.
MID(A, 2, 2)
MID('Some middle characters!', 3, 4)
MID(string, starting_at, extract_length)
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.
Returns the minimum value among the passed arguments.
MIN(10, 11)
MIN(Old_Data, New_Data)
MIN(value1, [value2, ...])
value1: The first number or column to consider for the minumum value.
value2, ... [OPTIONAL]: Additional numbers or columns to compute the minumum value from.
Returns the minute component of a specific date, as a number.
MINUTE(date_column)
MINUTE('2012-12-22 09:45:00')
MINUTE(date)
date: The date or date series to get the minute of.
Returns the month that a specific date falls in, as a number.
MONTH(date_column)
MONTH('2012-12-22')
MONTH(date)
date: The date or date series to get the month of.
Returns the product of two numbers.
MULTIPLY(2,3)
MULTIPLY(A,3)
MULTIPLY(factor1, [factor2, ...])
factor1: The first number to multiply.
factor2, ... [OPTIONAL]: Additional numbers or series to multiply.
Returns True if any of the provided arguments are True, and False if all of the provided arguments are False.
OR(True, False)
OR(Status == 'success', Status == 'pass', Status == 'passed')
OR(boolean_condition1, [boolean_condition2, ...])
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.
The POWER function can be used to raise a number to a given power.
POWER(4, 1/2)
POWER(Dose, 2)
POWER(value, exponent)
value: Number to raise to a power.
exponent: The number to raise value to.
Capitalizes the first letter of each word in a specified string.
=PROPER('nate nush')
=PROPER(A)
PROPER(string)
string: The value or series to convert to convert to proper case.
Returns the quarter (1-4) that a specific date falls in, as a number.
QUARTER(date_column)
QUARTER('2012-12-22')
QUARTER(date)
date: The date or date series to get the quarter of.
Returns a substring from the beginning of a specified string.
RIGHT(A, 2)
RIGHT('The last character!')
RIGHT(string, [number_of_characters])
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.
Rounds a number to a given number of decimals.
ROUND(1.3)
ROUND(A, 2)
ROUND(value, [decimals])
value: The value or series to round.
decimals: The number of decimals to round to. Default is 0.
Returns the seconds component of a specific date, as a number.
SECOND(date_column)
SECOND('2012-12-22 09:23:05')
SECOND(date)
date: The date or date series to get the seconds of.
Computes the skew of a series, excluding missing values.
=SKEW(A)
=SKEW(A * B)
SKEW(series)
series: The series to calculate the skew of.
Given a date, returns the most recent start of the business month, as a state. E.g. the first weekday.
STARTOFBUSINESSMONTH(date_column)
STARTOFBUSINESSMONTH('2012-12-22 09:23:05')
STARTOFBUSINESSMONTH(date)
date: The date or date series to get the most recent beginning of month business day of.
Given a date, returns the start of the month, as a date. E.g. input of 12-22-1997 will return 12-1-1997.
STARTOFMONTH(date_column)
STARTOFMONTH('2012-12-22 09:23:05')
STARTOFMONTH(date)
date: The date or date series to get the first day of the month of.
Computes the standard deviation of a series, excluding missing values.
=STDEV(A)
=STDEV(A * B)
STDEV(series)
series: The series to calculate the standard deviation of.
Returns the date with a seconds, minutes, and hours component of 00:00:00.
STRIPTIMETODAYS(date_column)
STRIPTIMETODAYS('2012-12-22 09:23:05')
STRIPTIMETODAYS(date)
date: The date or date series to reset the seconds, minutes, and hours component of.
Returns the date with a seconds and minutes component of 00:00.
STRIPTIMETOHOURS(date_column)
STRIPTIMETOHOURS('2012-12-22 09:23:05')
STRIPTIMETOHOURS(date)
date: The date or date series to reset the seconds and minutes component of.
Returns the date with a seconds component of 00.
STRIPTIMETOMINUTES(date_column)
STRIPTIMETOMINUTES('2012-12-22 09:23:05')
STRIPTIMETOMINUTES(date)
date: The date or date series to reset the seconds component of.
Returns the date adjusted to the start of the month.
STRIPTIMETOMONTHS(date_column)
STRIPTIMETOMONTHS('2012-12-22 09:23:05')
STRIPTIMETOMONTHS(date)
date: The date or date series to reset the seconds, minutes, hours, and days of.
Returns the date adjusted to the start of the year.
STRIPTIMETOYEARS(date_column)
STRIPTIMETOYEARS('2012-12-22 09:23:05')
STRIPTIMETOYEARS(date)
date: The date or date series to reset the seconds, minutes, hours, days, and month components of.
Replaces existing text with new text in a string.
SUBSTITUTE('Better great than never', 'great', 'late')
SUBSTITUTE(A, 'dog', 'cat')
SUBSTITUTE(text_to_search, search_for, replace_with, [count])
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.
Returns the sum of the given numbers and series.
SUM(10, 11)
SUM(A, B, D, F)
SUM(A, B, D, F)
SUM(value1, [value2, ...])
value1: The first number or column to add together.
value2, ... [OPTIONAL]: Additional numbers or columns to sum.
Turns the passed series into a string.
=TEXT(Product_Number)
=TEXT(Start_Date)
TEXT(series)
series: The series to convert to a string.
Returns a string with the leading and trailing whitespace removed.
=TRIM(' ABC')
=TRIM(' ABC ')
=TRIM(A)
TRIM(string)
string: The value or series to remove the leading and trailing whitespace from.
Returns the type of each element of the passed series. Return values are 'number', 'str', 'bool', 'datetime', 'object', or 'NaN'.
TYPE(Nums_and_Strings)
IF(TYPE(Account_Numbers) != 'NaN', Account_Numbers, 0)
TYPE(series)
series: The series to get the type of each element of.
Converts a given string to uppercase.
=UPPER('abc')
=UPPER(A)
=UPPER('Nate Rush')
UPPER(string)
string: The string or series to convert to uppercase.
Converts a string series to a number series. Any values that fail to convert will return an NaN.
=VALUE(A)
=VALUE('123')
VALUE(string)
string: The string or series to convert to a number.
Computes the variance of a series, excluding missing values.
=VAR(A)
=VAR(A - B)
VAR(series)
series: The series to calculate the variance of.
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.
VLOOKUP(Names0, Other_Dataframe!Ids:Ages, 1)
VLOOKUP('John Smith', Names:Ages, 2)
VLOOKUP(Names0, Ids:Ages, Column Indexes0)
VLOOKUP(lookup_value, where, index)
lookup_value: The value to look up.
where: The range to look up in.
index: The column index to return.
Returns the week (1-52) of a specific date, as a number.
WEEK(date_column)
WEEK('2012-12-22 09:23:05')
WEEK(date)
date: The date or date series to get the week of.
Returns the day of the week that a specific date falls on. 1-7 corresponds to Monday-Sunday.
WEEKDAY(date_column)
WEEKDAY('2012-12-22')
WEEKDAY(date)
date: The date or date series to get the weekday of.
Returns the day of the year that a specific date falls in, as a number.
YEAR(date_column)
YEAR('2012-12-22')
YEAR(date)
date: The date or date series to get the month of.
Missing a formula? if your workflow requires formulas that Mito does not yet support. We prioritize adding functionality for active Mito users!