Skip to main content

Transform with Excel Formulas

Visitran provides a comprehensive set of Excel Formulas to help you with data transformations. The formulas are organized into the following categories:

Logical Functions​

  • AND - Returns TRUE if all arguments are TRUE
  • CHOOSE - Chooses a value from a list of values
  • IF - Specifies a logical test to perform
  • IFNA - Returns a specified value if the expression is #N/A
  • IFS - Checks multiple conditions
  • NOT - Reverses the logic of its argument
  • OR - Returns TRUE if any argument is TRUE
  • SWITCH - Evaluates an expression against a list of values
  • XOR - Returns a logical exclusive OR

Text Functions​

  • CLEAN - Removes non-printable characters
  • CODE - Returns the numeric code for the first character
  • CONCAT - Joins text items into one text item
  • CONCATENATE - Joins several text strings into one
  • EXACT - Checks if two text values are identical
  • FIND - Finds text within another text string (case-sensitive)
  • FIXED - Formats a number as text with fixed decimals
  • LEFT - Returns characters from start of text string
  • LEN - Returns the number of characters in a text string
  • LOWER - Converts text to lowercase
  • MID - Returns specific characters from a text string
  • PROPER - Capitalizes the first letter in each word
  • REPT - Repeats text a given number of times
  • RIGHT - Returns characters from end of text string
  • SEARCH - Finds text within another text string (not case-sensitive)
  • SUBSTITUTE - Substitutes new text for old text
  • TRIM - Removes spaces from text
  • UPPER - Converts text to uppercase

Date and Time Functions​

  • DATE - Returns the serial number of a date
  • DAY - Returns the day of a date
  • DAYS - Returns the number of days between two dates
  • EDATE - Returns a date a specified number of months before/after
  • HOUR - Returns the hour component of a time
  • MINUTE - Returns the minutes component of a time
  • MONTH - Returns the month component of a date
  • NOW - Returns the current date and time
  • SECOND - Returns the seconds component of a time
  • TIME - Returns the serial number of a time
  • TODAY - Returns today's date
  • WEEKDAY - Returns the day of the week
  • WEEKNUM - Returns the week number of the year
  • YEAR - Returns the year component of a date

Information Functions​

  • ISBLANK - Returns TRUE if value is blank
  • ISEVEN - Returns TRUE if number is even
  • ISNA - Returns TRUE if value is #N/A
  • ISNUMBER - Returns TRUE if value is a number
  • ISODD - Returns TRUE if number is odd
  • ISTEXT - Returns TRUE if value is text
  • N - Returns a value converted to a number

Mathematical Functions​

Basic Math​

  • ABS - Returns the absolute value of a number
  • CEILING - Rounds a number up to the nearest multiple
  • FLOOR - Rounds a number down to the nearest multiple
  • INT - Rounds a number down to the nearest integer
  • MOD - Returns the remainder after division
  • POWER - Returns a number raised to a power
  • PRODUCT - Multiplies all numbers given as arguments
  • QUOTIENT - Returns the integer portion of a division
  • ROUND - Rounds a number to a specified number of digits
  • ROUNDDOWN - Rounds a number down toward zero
  • ROUNDUP - Rounds a number up away from zero
  • SIGN - Returns the sign of a number
  • SQRT - Returns the square root of a number
  • SQRTPI - Returns the square root of (number * pi)

Trigonometry​

  • ACOS - Returns the arccosine of a number
  • ASIN - Returns the arcsine of a number
  • ATAN - Returns the arctangent of a number
  • ATAN2 - Returns the arctangent from x- and y-coordinates
  • COS - Returns the cosine of a number
  • COT - Returns the cotangent of an angle
  • DEGREES - Converts radians to degrees
  • PI - Returns the value of pi
  • RADIANS - Converts degrees to radians
  • SIN - Returns the sine of an angle
  • TAN - Returns the tangent of an angle

Statistical​

  • AVERAGE - Returns the average of its arguments
  • MAX - Returns the maximum value in a list of arguments
  • MIN - Returns the minimum value in a list of arguments
  • SUM - Adds its arguments
  • SUMSQ - Returns the sum of the squares of the arguments

Bitwise Operations​

  • BITAND - Returns a bitwise AND of two numbers
  • BITLSHIFT - Returns a number shifted left by specified bits
  • BITOR - Returns a bitwise OR of two numbers
  • BITRSHIFT - Returns a number shifted right by specified bits
  • BITXOR - Returns a bitwise XOR of two numbers

Type Conversion and Validation​

  • CAST - Converts a value to a specified type
  • COALESCE - Returns first non-null value
  • ISIN - Checks if a value exists in a list
  • NOTIN - Checks if a value does not exist in a list
  • NUMBERVALUE - Converts text to number in locale-independent way