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.

Formula	Description
`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
`FALSE`	Returns FALSE
`TRUE`	Returns TRUE