Calculated Column Formulas

Dig deeper into your data with advanced formulas

Formulas are the best way to take your data to the next level and speed up your analysis. Rather than exporting your data into another program, perform calculations directly within Mapline! Each formula is formatted first with the formula name in all caps, followed by parentheses containing any required arguments. Mapline has an extensive selection of formulas that can be used, including many that are not available in other spreadsheet software.

All formulas that can be used within calculated columns are listed here alphabetically. Just as in Microsoft Excel, multiple formulas can be nested within each other to return more complex results.
FormulaDescription
ABS(number)Returns the absolute value of a number
ACCRINT(issue, first, settlement, rate, par, frequency, basis)Returns the accrued interest for a security that pays periodic interest
ACOS(number)Returns the arccosine of a number
ACOSH(number)Returns the inverse hyperbolic cosine of a number
ACOT(number)Returns the arccotangent of a number
ACOTH(number)Returns the hyperbolic arccotangent of a number
ADD(num1, num2)Returns the sum of two given numbers
AND(arg_1, arg_2, ..., arg_N)Returns TRUE if all of its arguments are TRUE
ARABIC(text)Converts a Roman number to Arabic, as a number
ASIN(number)Returns the arcsine of a number
ASINH(number)Returns the inverse hyperbolic sine of a number
ATAN(number)Returns the arctangent of a number
ATAN2(number_x, number_y)Returns the arctangent from x- and y-coordinates
ATANH(number)Returns the inverse hyperbolic tangent of a number
AVERAGE(param1,param2..)Returns the average of its arguments
BASE(number, radix, min_length)Converts a number into a text representation with the given radix (base)
BIN2DEC(number)Converts a binary number to decimal
BIN2HEX(number, places)Converts a binary number to hexadecimal
BIN2OCT(number, places)Converts a binary number to octal
BITAND(number1, number2)Returns a ‘Bitwise And’ of two numbers
BITLSHIFT(number, shift)Returns a value number shifted left by shift_amount bits
BITOR(number1, number2)Returns a bitwise OR of 2 numbers
BITRSHIFT(number, shift)Returns a value number shifted right by shift_amount bits
BITXOR(number1, number2)Returns a bitwise ‘Exclusive Or’ of two numbers
CEILING(number, significance, mode)Rounds a number to the nearest integer or to the nearest multiple of significance
CHAR(number)Returns the character specified by the code number
CLEAN(text)Removes all nonprintable characters from text
CODE(text)Returns a numeric code for the first character in a text string
COMBIN(number, number_chosen)Returns the number of combinations for a given number of objects
COMPLEX(real, imaginary, suffix)Converts real and imaginary coefficients into a complex number
CONCATENATE(param1,param2...)Joins several text items into one text item
CONVERT(number, from_unit, to_unit)Converts a number from one measurement system to another
COS(number)Returns the cosine of a number
COSH(number)Returns the hyperbolic cosine of a number
COT(number)Returns the hyperbolic cosine of a number
COTH(number)Returns the cotangent of an angle
CSC(number)Returns the cosecant of an angle
CSCH(number)Returns the hyperbolic cosecant of an angle
DATE(year, month, day)Returns the serial number of a particular date
DATEVALUE(date_text)Converts a date in the form of text to a serial number
DAY(date_serial)Converts a serial number to a day of the month
DAYS(end_date, start_date)Returns the number of days between two dates
DAYS360(start_date, end_date, method)Calculates the number of days between two dates based on a 360-day year
DEC2BIN(number, places)Converts a decimal number to binary
DEC2HEX(number, places)Converts a decimal number to hexadecimal
DEC2OCT(number, places)Converts a decimal number to octal
DECIMAL(number, radix)Converts a text representation of a number in a given base into a decimal number
DEGREES(number)Converts radians to degrees
DELTA(number1, number2)Tests whether two values are equal
DIVIDE(num1, num2)Returns the results of num1 divided by num2
E()Euler’s number 2.71828
EDATE(start_date, months)Returns the serial number of the date that is the indicated number of months before or after the start date
EOMONTH(start_date, months)Returns the serial number of the last day of the month before or after a specified number of months
EQ(val1, val2)Returns TRUE if val1 is equal to val2
EVEN(number)Rounds a number up to the nearest even integer
EXACT(text1, text2)Checks to see if two text values are identical
EXP(number)ReturnsĀ eĀ raised to the power of a given number
EXTRACTEMAIL(inputstring,email_instance)Returns all emails within a text
FACT(number)Returns the factorial of a number
FACTDOUBLE(number)Returns the double factorial of a number
FIND(find_text, within_text, position)Finds one text value within another (case-sensitive)
FIRSTDATEOFWEEK(date_serial)Returns the First Date of the Week
LASTDATEOFWEEK(date_serial)Returns the Last Date of the Week
FIRSTDATEOFMONTH(date_serial)Returns the First Date of the Month
LASTDATEOFMONTH(date_serial)Returns the Last Date of the Month
FIRSTDATEOFTRIMESTER(date_serial)Returns the First Date of the Trimester
LASTDATEOFTRIMESTER(date_serial)Returns the Last Date of the Trimester
FIRSTDATEOFQUARTER(date_serial)Returns the First Date of the Quarter
LASTDATEOFQUARTER(date_serial)Returns the Last Date of the Quarter
FIRSTDATEOFYEAR(date_serial)Returns the First Date of the Year
LASTDATEOFYEAR(date_serial)Returns the Last Date of the Year
FLOOR(number, significance)Rounds a number down, toward zero
GCD(param1,param2,...)Returns the greatest common divisor
GEOMEAN(param1,param2,...)Returns the geometric mean
HARMEAN(param1,param2,...)Returns the harmonic mean
HEX2BIN(number, places)Converts a hexadecimal number to binary
HEX2DEC(number)Converts a hexadecimal number to decimal
HEX2OCT(number, places)Converts a hexadecimal number to octal
HOUR(date_serial)Converts a serial number to an hour
IF(logical_expression, value_if_true, value_if_false)Specifies a logical test to perform
IFBLANK(value,value_if_blank)Returns value_if_blank if the value is TRUE else return value
IFERROR(value,value_if_error)Returns value_if_error if the value is error
IFVALUE(logical_text,value,if_true)Returns if_true if the logical_text is TRUE else return the true value
IMAGINARY(inumber)Returns the imaginary coefficient of a complex number
IMCONJUGATE(inumber)Returns the complex conjugate of a complex number
IMCOS(inumber)Returns the cosine of a complex number
IMCOSH(inumber)Returns the hyperbolic cosine of a complex number
IMCOT(inumber)Returns the cotangent of a complex number
IMCSC(complex number)Returns the cosecant of a complex number
IMCSCH(complex number)Returns the hyperbolic cosecant of a complex number
IMDIV(inumber1, inumber2)Returns the quotient of two complex numbers
IMEXP(inumber)Returns the exponential of a complex number
IMLN(inumber)Returns the natural logarithm of a complex number
IMLOG10(inumber)Returns the base-10 logarithm of a complex number
IMLOG2(inumber)Returns the base-2 logarithm of a complex number
IMPOWER(inumber, number)Returns a complex number raised to an integer power
IMPRODUCT(inumber1,inumber2)Returns the product of complex numbers
IMREAL(inumber)Returns the real coefficient of a complex number
IMSEC(inumber)Returns the secant of a complex number
IMSECH(inumber)Returns the hyperbolic secant of a complex number
IMSIN(inumber)Returns the sine of a complex number
IMSINH(inumber)Returns the hyperbolic sine of a complex number
IMSQRT(inumber)Returns the square root of a complex number
IMSUB(inumber1, inumber2)Returns the difference between two complex numbers
IMSUM(inumber1, inumber2)Returns the difference between two complex numbers
IMTAN(inumber)Returns the tangent of a complex number
INT(number)Rounds a number down to the nearest integer
ISBINARY(number)Returns true if value is in the binary number system (eg
ISBLANK(value)Returns TRUE if the value is blank
ISEVEN(number)Returns TRUE if the number is even
ISERR(value)Returns TRUE if the value is error other than N/A
ISERROR(value)Returns TRUE if the value is error
ISLOGICAL(value)Returns TRUE if the value is a logical value
ISNA(value)Returns TRUE if the value is NA
ISNONTEXT(value)Returns TRUE if the value is not text
ISNUMBER(value)Returns TRUE if the value is a number
ISODD(number)Returns TRUE if the number is odd
ISOWEEKNUM(date)Returns the number of the ISO week number of the year for a given date
ISTEXT(value)Returns TRUE if the value is text
LCM(number1,number2,...)Returns the least common multiple
LEFT(text, number)Returns the leftmost characters from a text value
LEN(text)Returns the number of characters in a text string
LN(number)Returns the natural logarithm of a number
LOG(number, base)Returns the logarithm of a number to a specified base
LOG10(number)Returns the base-10 logarithm of a number
LOWER(text)Converts text to lowercase
MAX(number1,number2,...)Returns the maximum value in a list of arguments
MID(text, start, number)Returns a specific number of characters from a text string starting at the position you specify
MIN(number1,number2,...)Returns the minimum value in a list of arguments
MINUS(num1, num2)Returns the results of num1 minus num2
MINUTE(date_serial)Converts a serial number to a minute
MOD(dividend, divisor)Returns the remainder from division
MONTH(date_serial)Converts a serial number to a month
QUARTER(date_serial)Converts a serial number to a quarter
MROUND(number, multiple)Returns a number rounded to the desired multiple
MULTIPLY(num1, num2)Returns the result of num1 multiplied by num2
NETWORKDAYS(start_date, end_date, holidays)Returns the number of whole workdays between two dates, assuming Saturday and Sunday are none working days
NETWORKDAYS.INTL(start_date, end_date, weekend_type, holidays)Returns the number of whole workdays between two dates
NOT(logical)Reverses the logic of its argument
NOW()Returns the serial number of the current date and time
OCT2BIN(number, places)Converts an octal number to binary
OCT2DEC(number)Converts an octal number to decimal
OCT2HEX(number, places)Converts an octal number to hexadecimal
ODD(number)Rounds a number up to the nearest odd integer
OR(param1,param2,...)Returns TRUE if any argument is TRUE
PERMUTATIONA(number, number_chosen)Returns the number of permutations for a given number of objects (with repetitions) that can be selected from the total objects
PI()Returns the value of pi
POWER(number, power)Returns the result of a number raised to a power
PRODUCT(number1,number2,...)Multiplies its arguments
PROPER(text)Capitalizes the first letter in each word of a text value
QUOTIENT(numerator, denominator)Returns the integer portion of a division
RADIANS(number)Converts degrees to radians
RAND()Returns a random number between 0 and 1
RANDBETWEEN(bottom, top)Returns a random number between the numbers you specify
REPLACE(text, position, length, new_text)Replaces characters within text
REPT(text, number)Repeats text a given number of times
RIGHT(text, number)Returns the rightmost characters from a text value
ROMAN(number)Converts an arabic numeral to roman, as text
ROUND(number, digits)Rounds a number to a specified number of digits
ROUNDDOWN(number, digits)Rounds a number down, toward zero
ROUNDUP(number, digits)Rounds a number up, away from zero
SEARCH(find_text, within_text, position)Finds one text value within another (not case-sensitive)
SEC(number)Returns the secant of an angle
SECH(number)Returns the hyperbolic secant of an angle
SECOND(date_serial)Converts a serial number to a second
SIGN(number)Returns the sign of a number
SIN(number)Returns the sine of the given angle
SINH(number)Returns the hyperbolic sine of a number
SQRT(number)Returns a positive square root
SQRTPI(number)Returns the square root of (number * pi)
SUBSTITUTE(text, old_text, new_text, occurrence)Substitutes new text for old text in a text string
SUM(param1,param2..)Adds its arguments
SUMSQ(number1,number2,...)Returns the sum of the squares of the arguments
T(value)Converts its arguments to text
TAN(number)Returns the tangent of a number
TANH(number)Returns the hyperbolic tangent of a number
TBILLEQ(settlement, maturity, discount)Returns the bond-equivalent yield for a Treasury bill
TIME(hour, minute, second)Returns the serial number of a particular time
TIMEVALUE(time_text)Converts a time in the form of text to a serial number
TODAY()Returns the serial number of today’s date
TRIM(text)Removes spaces from text
TRUNC(number, digits)Truncates a number to an integer
UNICHAR(number)Returns the Unicode character that is references by the given numeric value
UNICODE(text)Returns the number (code point) that corresponds to the first character of the text
UPPER(text)Converts text to uppercase
WEEKDAY(date_serial, return_type)Converts a serial number to a day of the week
WEEKNUM(date_serial, return_type)Converts a serial number to a number representing where the week falls numerically with a year
WORKDAY(start_date, days, holidays)Returns the serial number of the date before or after a specified number of workdays, assuming Saturday and Sunday are none working days
WORKDAY.INTL(start_date, days, weekend_type, holidays)Returns the serial number of the date before or after a specified number of workdays
XOR(param1,param2,...)Returns a logical exclusive OR of all arguments
YEAR(date_serial)Converts a serial number to a year
YEARFRAC(start_date, end_date, basis)Returns the year fraction representing the number of whole days between start_date and end_date
FALSEReturns FALSE
TRUEReturns TRUE