Calculated columns
Calculated Column formulas can be used to perform a wide range of operations on data, from simple arithmetic to complex logical and statistical computations. Whether you're rounding numbers, extracting portions of dates, calculating averages, or transforming texts, these formulas provide the versatility to generate the data you need.
Here are some typical use cases for Calculated Column functions:
Data Cleaning and Transformation: Effortlessly clean and reformat data, such as trimming whitespace, changing text casing, or converting date formats.
Statistical Analysis: Perform statistical calculations like averages, variances, or standard deviations to derive insights from numerical data sets.
Logical Operations: Apply logical tests to data to create flags, indicators, or to filter and categorize data based on specific criteria.
Mathematical Operations: Execute a variety of mathematical operations, enabling complex calculations like financial modelling or engineering calculations.
Text and Date Manipulation: Extract or transform portions of text and date fields, which is particularly useful in data preparation for reporting or further analysis.
Note that when a calculated column is applied to generated data, it is processed last. This ensures that all generated data and applied mockers are fully considered before the calculation occurs, preserving the intended transformations and logic.
Syntax
Calculated Columns are composed of a library of functions and operators used to build formulas and expressions in Syntho. When creating calculated columns in Syntho, you must follow specific syntax rules and best practices. Here are the fundamental syntax rules for Calculated Column expressions when creating calculated columns:
Formula Structure: An example Calculated Column formula is:
IF(AND([GENDER]="Female", [Age] > 18), MOCK_FIRST_NAME_FEMALE, MOCK_FIRST_NAME_MALE)References:
Column References: When referring to a column within the same table, you can just use the column name in square brackets:
[ColumnName].Table References: If you need to reference a column in a different table, use the table name followed by the column name in square brackets:
TableName[ColumnName].If your table names use spaces or special characters, enclose the name in single quotes.
Schema References: References to tables outside of the existing schema are not supported.
Functions: Calculated Column includes a wide range of functions that can be used in your expressions, such as time intelligence functions (
DATE,DATEADD), logical functions (IF,AND,OR), and many more. Functions follow the syntax ofFUNCTION_NAME(arguments).Mockers: Mockers can be added using the autosuggestion dropdown, using the syntax
MOCK_MOCKER_NAME, where MOCKER_NAME should be replaced with the relevant mocker name (e.g. FIRST_NAME). Any applied mockers in calculated columns will use the default mocker parameter settings. For each mocker, there is a mocker variant with and without consistent mapping. To enable consistent mapping, use the variant:MOCK_CONSISTENT_ MOCKER_NAME.Operators: Calculated Column supports various operators for arithmetic calculations (
+,-,*,/), and comparison (<,>,<=,>=,=,<>) operations.Constants: You can use constants like numbers (
123), strings ("text"), and dates (DATE(2020, 12, 31)).Whitespace and Line Breaks: Whitespace and line breaks do not affect the interpretation of the formula but can make your formulas easier to read.
Here's an example of a calculated column formula that calculates the total sales plus tax for each row in a table:
In this formula, [Total Sales] and [Tax Rate] are existing columns in the same table, and the formula calculates the total sales including tax for each row in the table.
Another example is the first name mocker. Imagine having a table with a column for first names. However, the user wants to generate male mock data for male names and female mock data for female names based on checking their gender in the gender column. This request can be expressed using the formula below:
If you would like to add a mocker to a calculated column formula, type "MOCK_" and you will see the names of mockers in the dropdown list. Either choose from the list or add the name of the mocker to the end of "MOCK_", with whitespaces replaced by underscores. Therefore, mockers like "First name female" and "Company Email" should be entered as "MOCK_FIRST_NAME_FEMALE" and "MOCK_COMPANY_EMAIL," respectively. A complete list of mockers can be found in the Mockers section.
Using Consistently Mapped Mockers
All mockers have variants to support consistent mapping. This ensures that input from the same column is consistently mocked.
Example:
The existing
MOCK_FIRST_NAMEpredefined mocker has a variantMOCK_CONSISTENT_FIRST_NAME. This new mocker consistently maps and mocks input from the same column where it is attached.
Supported calculated column functions
ABS
Returns the absolute value of a number.
Syntax
Parameters
number
Any number, column or mock value.
Return value
An absolute value of the number.
Example
The following formulas calculate absolute values of the column "HEALTHCARE_COVERAGE", -5 and mock value.
AND
The AND function returns TRUE if all provided arguments evaluate to TRUE, and FALSE if any argument evaluates to FALSE.
A common use case for the AND function is enhancing other functions that perform logical tests. For instance, the IF function tests a condition and returns one value if the condition is TRUE and another value if it is FALSE. By using AND as the logical_test argument in the IF function, you can test multiple conditions simultaneously rather than just one.
Syntax
Parameters
Logicalvalue1
The first condition to evaluate, which must result in either TRUE or FALSE.
Logicalvalue2
Additional conditions to evaluate, up to a maximum of 30, each of which must result in either TRUE or FALSE.
Return value
Returns TRUE if all arguments are TRUE.
Example
The following formula will return TRUE if gender of the person is male and his healthcare coverage value is more than 100. If any of the conditions fails, then it will return FALSE.
BASE
Converts a number into its text representation in the specified radix (base).
Syntax
Parameters
number
The number, column or mock value to be converted. It must be an integer greater than or equal to 0 and less than 2^53.
radix
The base (radix) to convert the number into. It must be an integer between 2 and 36, inclusive.
MinimumLength
The minimum length of the returned string. It must be an integer greater than or equal to 0.
Return value
Converts a positive integer to a specified base into a text from the numbering system.
Example
The following formulas calculate the text representation of the number in the specified radix (base).
BASE(5, 2) converts the decimal number 5 to base 2 (binary). Result is 101.
BASE(5, 2, 8) converts 5 into 101 and add 5 leading zeros to make the string 8 characters long, so answer should be 00000101.
BASE(42,16) converts 42 to base 16 (hexadecimal). The result is 2A.
Alternatively, use columns or mock values.
CEILING
Rounds a number up, away from zero, to the nearest multiple of a specified significance. The function always rounds a value up, away from zero, regardless of the sign of the number. If the number is an exact multiple of the significance, no rounding is performed. If both the number and significance are negative, the value is rounded down, away from zero. If the number is negative and the significance is positive, the value is rounded up toward zero.
Syntax
Parameters
number
The column, number or mock value to be rounded.
significance
The multiple to which you want to round the number.
Return value
A number rounded up to the nearest multiple of Significance.
Example
The following formulas calculates different values rounded up to the nearest multiple of Significance.
Rounds 3.3 up to nearest multiple of 1
Rounds -4.5 up to nearest multiple of -2
Rounds -2.5 up to nearest multiple of 2
Rounds 5.5 up to nearest multiple of 0.1
Rounds 0.9345 up to nearest multiple of 0.001
Rounds column N up to nearest multiple of column Significance.
CHAR
Returns the character corresponding to a specified number.
Syntax
Parameters
number
A number, column or mock value between 1 and 255 that specifies the character you want, based on the character set used by your computer.
Return value
A number converted into a character according to the current code table.
Example
The following formula converts any number like 77 or 37 into a character according to the current code table which are letter "M" and symbol of percentage "%", respectively.
CLEAN
Removes all nonprintable characters from a given text. The CLEAN function is useful for cleaning text imported from other applications that may contain characters not supported by your operating system. For example, it can remove low-level computer code that often appears at the beginning or end of data files and cannot be printed.
Important
The CLEAN function is designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31). However, in the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157) that the CLEAN function does not remove.
Syntax
Parameters
number
The number, column or mock values from which you want to remove nonprintable characters.
Return value
The text that has been "cleaned" of line breaks and other non-printable characters.
Example
The following formula returns the text that has been "cleaned" which is "Patient Medications". You can use columns or mock values as well.
CONCATENATE
The CONCATENATE function joins two or more text strings into a single string.
Syntax
Parameters
text1
The first item to join. This can be a text, column or mock values.
text2, ...text30
Additional text items to join. You can include up to 30 items, with a total character limit of 8,192.
Return value
Several text strings combined into one string.
Example
The following formula combines several text strings into one string. For example, concatenating state and county can result in texts such as "Massachusetts, Bristol County". Alternatively, use texts or mock values.
DATE
The DATE function creates a date value by combining individual year, month, and day components. This function is useful for combining separate year, month, and day values into a single date.
Syntax
Parameters
year
A number, column or mock value representing the year.
month
A number, column or mock value column representing the month of the year.
day
A number, column or mock value column representing the day of the month.
Return value
The specified date as the number of full days since nullDate.
Example
The following formula calculates the specified date as the number of full days since nullDate which is 2012-08-24 00:00:00.
DATEADD
Returns a column of dates, shifted forward or backward by the specified number of intervals.
Syntax
Parameters
dates
A column or mocker that contains dates.
number_of_intervals
An integer specifying the number of intervals to add or subtract from the dates.
interval
The interval used to shift the dates. Options include: year, quarter, month, or day.
Return value
A column of date values.
Example
The following formula calculates dates that are 20 years after the dates in the Birthdate column.
DATEVALUE
Description
The DATEVALUE function converts a date stored as text into a date. Note that the results of the DATEVALUE function may vary depending on your computer's system date settings. The DATEVALUE function is useful when you have dates in a text format that you want to filter, sort, format as dates, or use in date calculations.
Syntax
Parameters
date_text
A text column, string or mock representing a date.
Return value
A date that is converted from text to a date in datetime format.
Example
The following formula calculates a date that is converted from text or a column to a date in datetime format.
Alternatively, following formulas can be used as well:
DATEVALUE("6/24/2012") DATEVALUE("2012/6/24") DATEVALUE("24-JUNE-2024") DATEVALUE("24-JUNE")
DAY
The DAY function returns the day of a date represented by a serial number, with the day provided as an integer ranging from 1 to 31.
Syntax
Parameters
number
The date, column or mock date for which you want to find the day. Dates should be entered using the DATE function or as results from other formulas or functions.
Return value
The day of the given date value.
Example
The following formula calculates the day of the given date value which is 24.
DAYS
The DAYS function calculates the number of days between two dates.
Syntax
Parameters
date2
The later of the two dates column between which you want to calculate the number of days.
date1
The earlier of the two dates column between which you want to calculate the number of days.
Return value
The difference between two date values.
Example
The following formula calculates the difference between two date values which is 10.
DATEDIFF
The DateDiff function returns the number of time intervals between two specified dates.
Syntax
Parameters
date1, date2
An integer specifying the number of intervals to add or subtract from the dates.
interval
A string expression that specifies the time interval you want to use to calculate the difference between date1 and date2.
The value can be one of the following:
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
Return value
The number of units between two dates as defined in the interval.
Example
The following formula calculates the number of units between expiry and production dates in the interval of days:
DELTA
The DELTA function checks whether two values are equal. It returns 1 if number1 equals number2, and 0 otherwise.
Syntax
Parameters
number_1
The first number, column or mocker.
number_2
The second number, column or mocker. If omitted, column2 is assumed to be zero.
Return value
Returns TRUE (1) if both numbers are equal, otherwise returns FALSE (0).
Example
The following formula checks if a patient's healthcare coverage and expenses are equal. If equal, it will return 1, otherwise 0.
EVEN
The EVEN function rounds a number up to the nearest even integer. The function always rounds a value up and away from zero, regardless of its sign. If the number column is already an even integer, no rounding occurs.
Syntax
Parameters
number
The number, column or mocker to be rounded up to the nearest even integer.
Return value
A positive number rounded up to the next even integer and a negative number rounded down to the next even integer.
Example
The following formula rounds healthcare coverage to the nearest even integer.
FALSE
Returns the logical value FALSE.
Syntax
Return value
The logical value FALSE.
Example
The following formula returns the logical value FALSE.
FLOOR
The FLOOR function rounds a number down toward zero to the nearest multiple of a specified significance. When the number is positive, it is rounded down and adjusted toward zero. When the number is negative, it is rounded down and adjusted away from zero. If the number is already an exact multiple of the significance, no rounding occurs.
If either argument is nonnumeric, the function returns an error message.
If the
numberis positive and thesignificanceis negative, the function returns an error message.
Syntax
Parameters
number
The numeric number, column or mocker you want to round down.
significance
The multiple to which you want to round the number.
Return value
Rounds a number down to the nearest multiple of Significance.
Example
The following formulas round numbers down to the nearest multiple of Significance.
Rounds 7.7 down to nearest multiple of 3.
Rounds -3.4 down to nearest multiple of -3.
Rounds 1.453 down to nearest multiple of 0.01.
HEX2DEC
The HEX2DEC function converts a hexadecimal number to its decimal equivalent. If the input is not a valid hexadecimal number, the function returns an error.
Syntax
Parameters
number
The hexadecimal number, column or mocker column to convert. The number can have up to 10 characters (40 bits). The most significant bit represents the sign, and the remaining 39 bits represent the magnitude. Negative numbers are represented using two's-complement notation.
Return value
The decimal number for the hexadecimal number entered.
Example
The following formula calculates the decimal number for the hexadecimal number "CAB007", and result is 13283335.
HOUR
The HOUR function returns the hour component of a time value as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.). Time values are part of a date value and are represented as decimal numbers (e.g., 12:00 PM is represented as 0.5 because it is half of a day).
Syntax
Parameters
time
The time number, column or mocker from which you want to extract the hour. This can be entered as a text string within quotation marks (e.g., "6:45 PM"), as a decimal number (e.g., 0.78125 representing 6:45 PM), or as the result of other formulas or functions (e.g., TIMEVALUE("6:45 PM")).
Return value
Hour component of given time.
Example
The following formula returns hour component of given time which is 7. Alternatively, use columns to find hours for every row.
IF
The IF function performs a logical test and returns one value if the test evaluates to TRUE and another value if it evaluates to FALSE. This function is commonly used to create conditional formulas.
Syntax
Parameters
test
The condition you want to test. This can be any expression that returns either TRUE or FALSE.
then value
The column or mock value that is returned if the logical_test evaluates to TRUE.
otherwisevalue
The column or mock value that is returned if the logical_test evaluates to FALSE. If omitted, the function returns FALSE by default.
Return value
A logical test result.
Example
The following formula returns female mock first names if person is from Massachusetts and their healthcare coverage is more than 30, otherwise, it returns male mock first names.
IFS
The IFS function checks multiple conditions and returns a value corresponding to the first condition that evaluates to TRUE. This function simplifies the use of multiple nested IF statements, making it easier to read and manage multiple conditions.
To specify a default result, enter TRUE as the final condition argument. If none of the other conditions are met, the corresponding value will be returned. For example, if all other conditions fail, a default value can be returned by using TRUE as the final condition. If a condition argument is provided without a corresponding value, the function will return an error message. If a condition argument evaluates to something other than TRUE or FALSE, the function returns an error. If none of the conditions evaluate to TRUE, the function returns an error.
Syntax
Parameters
condition1
The condition that is evaluated to determine if it is TRUE or FALSE.
number1
The column or mock value returned if condition1 evaluates to TRUE. This can be left empty.
condition2…condition_N
Additional conditions that are evaluated to determine if they are TRUE or FALSE.
number2 …number_N
Th column or mock value returned if the corresponding conditionN evaluates to TRUE. Each valueN is associated with its corresponding conditionN. These can also be left empty.
Return value
A value that corresponds to the first true condition.
Example
The following formula returns mock female name if "GENDER" column of the table is coded as "F", and mock make name if the column is coded as "M".
ISBLANK
The ISBLANK function checks whether a value is empty and returns TRUE if the cell is blank. If the value contains any data, including a formula that returns an empty string (""), ISBLANK returns FALSE.
Syntax
Parameters
value
The value, column or mocker you want to check for emptiness.
Return value
TRUE if the value is blank; FALSE if it contains any data.
Example
The following formula checks if column ZIP is empty.
ISODD
The ISODD function checks whether a number is odd. It returns 1 if the number is odd, and 0 if the number is even. This function is useful for determining the parity of a number within a table. If the number is nonnumeric, ISODD returns an error.
Syntax
Parameters
value
The value or column to test. If the number is not an integer, it is truncated.
Return value
0 if the value is an even integer; 1 if the value is odd.
Example
The following formula checks if the value in Healthcare Expenses column is odd or even.
LEFT
The LEFT function returns a specified number of characters from the left side of a string. To determine the number of characters in a string, use the LEN function.
Syntax
Parameters
"text"
The string, column or mocker from which the leftmost characters are returned. If the string contains Null, the function returns Null.
number
A numeric expression or column indicating how many characters to return. If 0, a zero-length string ("") is returned. If the length is greater than or equal to the number of characters in the string, the entire string is returned.
Return value
The input text capped by a specified character length.
Example
The following formula returns random number of characters from the start of a text string in "STATE" column.
For example, is column filled with state "Massachusetts" for the next 6 rows, it will return random number of character from the start of "Massachusetts" such as, "Massach, Massa, Ma, Mass, Massachu, M".
LEN
The LEN function returns a Long value representing the number of characters in a string or the number of bytes required to store a variable.
Syntax
Parameters
"text"
Any valid string expression, column or mocker.
Return value
Length of a given text.
Example
The following formula the length value of Birthplace column.
LN
The LN function returns the natural logarithm of a specified number. Natural logarithms are based on the mathematical constant e (approximately 2.71828182845904). The LN function is the inverse of the EXP function.
Syntax
Parameters
number
The positive real number, column or mocker for which you want to calculate the natural logarithm.
Return value
The natural logarithm of the number.
Example
The following formula returns the natural logarithm of the numbers in the column of Healthcare Expenses.
LOG
The LOG function returns the logarithm of a specified number to a base that you define.
Syntax
Parameters
number
The positive real number, column or mocker for which you want to calculate the logarithm.
base
The base of the logarithm. If omitted, the base is assumed to be 10.
Return value
The logarithm of a number to the specified base.
Example
The following formula returns the logarithm of Healthcare Expenses column numbers to the base of 2.
LOG10
The LOG10 function returns the base-10 logarithm of a specified number.
Syntax
Parameters
number
The positive real number, column or mocker for which you want to calculate the base-10 logarithm.
Return value
The base-10 logarithm of a number.
Example
The following formula returns the base-10 logarithm of Healthcare Expenses column values or numbers.
LOWER
The LOWER function converts all uppercase letters in a text string to lowercase.
Syntax
Parameters
"text"
The text, column or mocker you want to convert to lowercase. The LOWER function does not alter characters in the text that are not letters.
Return value
Text converted to lowercase.
Example
The following formula returns "ADDRESS" column converted to lowercase texts.
MINUTE
The MINUTE function returns the minute component of a time value as an integer, ranging from 0 to 59. Time values are a portion of a date value and are represented as a decimal number (e.g., 12:00 PM is represented as 0.5 since it is half of a day).
Syntax
Parameters
time
The time, column or mocker from which you want to extract the minute. This can be entered as a text string (e.g., "4:30 PM"), as a decimal number (e.g., 0.78125 representing 6:45 PM), or as the result of other formulas or functions.
Return value
Minute component of given time.
Example
The following formula returns minute component of given time, 4:30:45 PM which is 30. Alternatively, use a column, such as "START" to return minutes.
MOD
Calculates the remainder of a division and always ensures it matches the sign of the divisor for consistency.
If the divisor is 0,
MODreturns the#DIV/0!error value.The
MODfunction can be expressed using theINTfunction as follows:MOD(n, d) = n - d*INT(n/d)
Syntax
Parameters
dividend
The number, column or mocker for which you want to find the remainder.
divisor
The number, column or mocker by which you want to divide the number.
Return value
The remainder when one integer is divided by another.
Example
The following formula returns the remainders when integers with different signs is divided by integers with different signs.
Remainder of 5/2
1
Remainder of -5/2. The sign is the same as divisor
1
Remainder of 5/-2. The sign is the same as divisor
-1
Remainder of -5/-2. The sign is the same as divisor
-1
MONTH
The MONTH function returns the month of a date represented by a serial number. The month is provided as an integer ranging from 1 (January) to 12 (December).
Values returned by the YEAR, MONTH, and DAY functions are based on the Gregorian calendar, regardless of the display format of the provided date.
Syntax
Parameters
number
The date, column or mocker from which you want to extract the month. Dates should be entered using the DATE function or as results from other formulas or functions (e.g., DATE(2008, 5, 23) for May 23, 2008). Entering dates as text can cause issues.
Return value
The month for the given date value.
Example
The following formula returns the month for the given date value such as birthdate column.
NOT
The NOT function reverses the logical value of its argument. If the argument is TRUE, NOT returns FALSE, and if the argument is FALSE, NOT returns TRUE. One common use of the NOT function is to enhance the functionality of other logical functions, such as IF. By using NOT within the logical test of an IF function, you can test multiple conditions and control the flow of logic based on reversed outcomes.
Syntax
Parameters
Logicalvalue
A column or expression that can be evaluated to TRUE or FALSE.
Return value
Complements (inverts) a logical value.
Example
The following formula returns mock female name if gender is not male and healthcare expenses is less than 100, otherwise, mock male name.
ODD
The ODD function rounds a number up to the nearest odd integer.
If the
numberis nonnumeric, the function returns an error.The function always rounds a value up and away from zero, regardless of its sign. If the
numberis already an odd integer, no rounding occurs.
Syntax
Parameters
number
The number, column or mocker to be rounded up to the nearest odd integer.
Return value
A positive number rounded up to the nearest odd integer and a negative number rounded down to the nearest odd integer.
Example
The following formulas return positive numbers rounded up to the nearest odd integers and negative numbers rounded down to the nearest odd integers.
Rounds 3.4 up to the nearest odd integer.
5
Rounds 3 up to the nearest odd integer.
3
Rounds 6 up to the nearest odd integer.
7
Rounds -3 up to the nearest odd integer.
-3
Rounds -4 up (away from 0) to the nearest odd integer.
-5
OR
The OR function returns TRUE if any of its arguments evaluate to TRUE, and returns FALSE if all arguments evaluate to FALSE.
The arguments must evaluate to logical values, such as
TRUEorFALSE, or be arrays that contain logical values.If an array or range argument contains text or empty values, those are ignored.
If no logical values are found in the specified range,
ORreturns an error.You can use an
ORarray formula to check if a value occurs within an array.
Syntax
Parameters
Logicalvalue1
The first condition to test, which can evaluate to either TRUE or FALSE.
Logicalvalue2 ...Logicalvalue30
Additional conditions to test, up to a maximum of 30 conditions, each of which can evaluate to either TRUE or FALSE.
Return value
TRUE if at least one argument is TRUE.
Example
The following formula will return TRUE if one of the conditions below is TRUE.
PI
The PI function returns the mathematical constant pi (π), accurate to 15 digits, which is approximately 3.14159265358979.
Syntax
Parameters
The PI function does not require any arguments. It simply returns the value of π.
Return value
Returns 3.14159265358979, the value of the mathematical constant PI to 14 decimal places.
Example
The following formula returns the area of a circle with the radius of 5 which is 78.53981633974483.
POWER
The POWER function returns the result of a base number raised to a specified power. You can also use the ^ operator as an alternative to the POWER function, such as 4^3, to indicate that the base number (4) is raised to the power of 3.
Syntax
Parameters
base
The base number, column or mocker, which can be any real number.
exponent
The exponent number, column or mocker to which the base number is raised.
Return value
A number raised to another number.
Example
The following formula raises 1.1 to 3 which is 1.331 or raises amount to exponent.
PRODUCT
The PRODUCT function multiplies all the numbers provided as arguments and returns the product. For example, if values are 3 and 4, you can use the formula PRODUCT(3, 4) to multiply these two numbers. The same operation can also be performed using the multiplication (*) operator, such as 3 * 4. The PRODUCT function is particularly useful when you need to multiply many values together.
Syntax
Parameters
number1
The first number, column, mocker or range that you want to multiply.
number2, ...number30
Additional number columns or ranges that you want to multiply, up to a maximum of 30 arguments.
Return value
Product of numbers.
Example
The following formula returns product of numbers which is 120. Or use columns to find their product result for every row.
Alternatively, the same result can be derived from the following formulas as well:
PRODUCT({2,3,4,5}) or 2*3*4*5
PROPER
The PROPER function capitalizes the first letter of each word in a text string and any other letters that follow a non-letter character. It converts all other letters to lowercase.
Syntax
Parameters
"text"
The text, column or mocker you want to format. This can be a text string enclosed in quotation marks, a formula that returns text, or a reference to a value containing the text you want to capitalize.
Return value
Capitalized words or texts.
Example
The following formula can capitalize any texts below which can be "5-Star 99Hotels Amsterdam", an address from the address column or mock address.
PV
The PV function returns the present value of an annuity based on periodic, fixed payments to be made in the future and a fixed interest rate. The present value is the total amount that a series of future payments is worth now.
An annuity is a series of fixed payments made over time, such as a mortgage or a savings plan.
The
rateandnperarguments must be based on the same units of time. For example, ifrateis calculated using months,npershould also be calculated using months.In financial functions, cash outflows (such as deposits or payments) are represented by negative numbers, while cash inflows (such as dividends) are represented by positive numbers.
Syntax
Parameters
Rate
The interest rate per period. For example, if you have a car loan with an annual percentage rate (APR) of 10% and make monthly payments, the rate per period is 0.1/12, or 0.0083.
Nper
The total number of payment periods in the annuity. For example, a two-year car loan with monthly payments has 2 * 12 = 24 payment periods.
Pmt
The payment made each period, which typically includes both principal and interest and remains constant over the life of the annuity.
Fv
The future value or cash balance you want after the final payment. For example, the future value of a loan is $0 after the final payment. If omitted, 0 is assumed.
Type
Specifies when payments are due—0 for payments at the end of the period, or 1 for payments at the beginning. If omitted, 0 is assumed.
Return value
The present value of an investment.
Example
The following formula calculates the present value of a 4-year loan with an annual interest rate of 6% and monthly payments of $250.99 is approximately $10687 or use relevant columns.
QUARTER
The QUARTER function returns the quarter of the year for a given date, represented as a number from 1 to 4, where:
1 corresponds to January – March,
2 corresponds to April – June,
3 corresponds to July – September,
4 corresponds to October – December.
Syntax
Parameters
date
The date, column or mocker for which you want to determine the quarter.
Return value
An integer from 1 to 4, representing the quarter of the year.
Example
The following formula returns 3 as 24 of August falls in 3rd quarter (July – September). Or use columns to find the quarters of every row.
QUOTIENT
The QUOTIENT function returns the integer portion of a division, discarding the remainder. This function is useful when you only need the whole number result of a division.
Syntax
Parameters
dividend
The dividend column, mocker or the number to be divided.
divisor
The divisor column, mocker or the number by which the numerator is divided.
Return value
Integer part of a division.
Example
The following formula returns -2.
RAND
The RAND function returns a random real number that is evenly distributed between 0 (inclusive) and 1 (exclusive). Each time the worksheet recalculates, a new random number is generated.
Syntax
Parameters
The RAND function does not require any arguments. To generate a random real number between two specified values a and b, use the following formula:
Return value
A random number between 0 and 1.
Example
The following formula returns random numbers between 3 and 5 which are 3.0243019580636967, 3.6078301717905408, 4.040270917581723 etc.
RANDBETWEEN
The RANDBETWEEN function returns a random integer between the specified lower and upper bounds. Each time the worksheet recalculates, a new random integer is generated.
Syntax
Parameters
lowerbound
The smallest integer, column or mocker that can be returned by the function.
upperbound
The largest integer, column or mocker that can be returned by the function.
Return value
A random integer between two numbers.
Example
The following formula returns numbers between 3 and 5 which are only 3, 4 or 5.
REPLACE
The REPLACE function replaces part of a text string with a different text string, based on the number of characters you specify.
Syntax
Parameters
old_text
The original text, column or mocker in which you want to replace some characters.
start_num
The position of the character in old_text that you want to start replacing with new_text.
num_chars
The number of characters in old_text that you want REPLACE to replace with new_text.
new_text
The text, column or mocker that will replace the specified characters or bytes in old_text.
Return value
Part of a string with a new string.
Example
The following first formula returns "Hello Moon!". The second formula adds "004A" at the end of the passport numbers and replaces 7 characters starting at 6th character.
RIGHT
The RIGHT function returns a specified number of characters from the right side of a string.
Syntax
Parameters
"text"
The text, column or mocker from which the rightmost characters are returned. If the string contains Null, the function returns Null.
number
A numeric expression indicating how many characters to return. If 0, a zero-length string ("") is returned. If the length is greater than or equal to the number of characters in the string, the entire string is returned.
Return value
The input text starting from a specified character index.
Example
The following first formula returns "Earth!". The second formula returns random number of characters starting from the right of the text (state name).
ROUND
The ROUND function rounds a number to a specified number of digits.
If
Countis greater than 0, theNumberis rounded to the specified number of decimal places.If
Countis 0, theNumberis rounded to the nearest integer.If
Countis less than 0, theNumberis rounded to the left of the decimal point.To always round up (away from zero), use the
ROUNDUPfunction.To always round down (toward zero), use the
ROUNDDOWNfunction.
Syntax
Parameters
number
The number, column or mocker you want to round.
count
The number of digits to which you want to round the number.
Return value
A number that is rounded to a certain number of decimal places.
Example
The following formulas round the numbers to various decimal places.
Rounds 3.25 to one decimal place
3.2
Rounds 5.149 to one decimal place
5.1
Rounds -6.745 to two decimal places
-6.75
Rounds 23.9 to one decimal place to the left of the decimal point
20
Rounds 575.9 to the nearest multiple of 1000
1000
Rounds 2.33 to the nearest multiple of 10
0
Rounds -51.52 to the nearest multiple of 100
-100
ROUNDDOWN
The ROUNDDOWN function rounds a number down, toward zero.
ROUNDDOWNbehaves similarly toROUND, except that it always rounds a number down.If
Countis greater than 0, thenumberis rounded down to the specified number of decimal places.If
Countis 0, thenumberis rounded down to the nearest integer.If
Countis less than 0, thenumberis rounded down to the left of the decimal point.
Syntax
Parameters
number
Any real number, column or mocker that you want to round down.
count
The number of digits to which you want to round the number.
Return value
A number rounded down, toward zero, to a certain precision.
Example
The following formulas round down the numbers to various decimal places.
Rounds 5.3 down to zero decimal place
5
Rounds 66.8 down to zero decimal place
66.8
Rounds 1.24279 down to three decimal places
1.242
Rounds -1.24279 down to one decimal places
-1.2
Rounds 12427.98637 down to 2 decimal places to the left of the decimal point.
12400
ROUNDUP
The ROUNDUP function rounds a number up, away from 0 (zero).
ROUNDUPbehaves similarly toROUND, except that it always rounds a number up.If
Countis greater than 0, theNumberis rounded up to the specified number of decimal places.If
Countis 0, theNumberis rounded up to the nearest integer.If
Countis less than 0, theNumberis rounded up to the left of the decimal point.
Syntax
Parameters
number
Any real number, column or mocker that you want rounded up.
count
The number of digits to which you want to round the number.
Return value
A number rounded up, away from zero, to a certain precision.
Example
The following formulas round up the numbers to various decimal places.
Rounds 5.3 up to zero decimal place
6
Rounds 66.8 up to zero decimal place
67
Rounds 1.24279 up to three decimal places
1.243
Rounds -1.24279 up to one decimal places
-1.3
Rounds 12427.98637 up to 2 decimal places to the left of the decimal point.
12500
SECOND
The SECOND function returns the seconds component of a time value, as an integer ranging from 0 to 59.
Syntax
Parameters
time
The time, column or mocker values from which you want to extract the seconds. This can be entered as a text string (e.g., "6:45 PM"), as a decimal number (e.g., 0.78125 representing 6:45 PM), or as the result of other formulas or functions (e.g., TIMEVALUE("6:45 PM")).
Return value
Second component of given time.
Example
The following formulas calculate second component of given time, which is 55 in the first formula. Regarding second one, it calculates according to the value provided in the start column.
SQRT
The SQRT function returns the positive square root of a given number.
Syntax
Parameters
number
The number, column or mocker for which you want to find the square root. If the number is negative, SQRT returns an error value.
Return value
The positive square root of a number.
Example
The following formula calculates the positive square root of a 49 which is 7. Alternatively, find square roots of all values in a give column, here "PAYER_COVERAGE".
SUBSTITUTE
The SUBSTITUTE function replaces specific text within a text string with new text. Use SUBSTITUTE when you want to replace specific occurrences of text in a string; use REPLACE when you want to replace text based on its position in the string.
Syntax
Parameters
text
The text string, column or mocker containing the text where you want to substitute characters.
old_text
The text you want to replace.
new_text
The text that will replace Old_text
occurrence
Specifies which occurrence of Old_text you want to replace with New_text. If you provide Occurrence, only that specific instance of Old_text is replaced. If omitted, all occurrences of Old_text in the text are replaced with New_text.
Return value
String where occurrences of Old_text are replaced by New_text. It replaces only specific occurrence if last parameter is provided.
Example
The following first formula substitutes third instance of "2" with "3" which returns "April 2, 2013". The second one, substitutes first instance of "X" with "S" which returns the first characters of passport numbers as "S".
SWITCH
The SWITCH function evaluates one value (referred to as the expression) against a list of values and returns the result corresponding to the first matching value. If no match is found, an optional default value may be returned.
Syntax
Parameters
expression1
The value (such as a number, date, text or column) that will be compared against value_1 through value_N.
value1, value2, ... value_N
The columns to be compared against Expression1 through expression_n.
result1…result_n
The values to be returned when the corresponding value1 through value_N arguments match expression1 through expression_n. Each result_n must be provided for each corresponding value_N argument.
default
The column to return if no matches are found in the value_N expressions. The Default argument is identified by having no corresponding result_n expression. It must be the final argument in the function.
Return value
A list of arguments, consisting of an expression followed by a value.
Example
The following formula returns "Horse".
TODAY
The TODAY function returns the current date.
Syntax
Parameters
The TODAY function has no arguments.
Return value
An integer representing the current date as the number of full days since nullDate.
Example
The following formula calculates the age a person who was born in 1998.
TRIM
The TRIM function removes all spaces from a text string except for single spaces between words. Use TRIM on text that may have irregular spacing, often when text is imported from another application.
Syntax
Parameters
"text"
The text, column or mocker from which you want to remove extra spaces.
Return value
All spaces that are removed from a text string.
Example
The following formulas remove leading and trailing spaces from the text below and return "Senatus Populusque Romanus", or remove "ADDRESS" column.
TRUE
The TRUE function returns the logical value TRUE. Use this function to return TRUE based on a condition.
For example:
IF(2=2,TRUE(), FALSE())returnsTRUEif the value 2 is equal to 2.
You can also enter the value TRUE directly into formulas without using the function.
For example:
IF(2=2,TRUE, FALSE)also returnsTRUEif the condition is met.
If the condition is not met, both examples return FALSE.
Syntax
Parameters
The TRUE function does not require any arguments.
Return value
The logical value that is set to TRUE.
Example
The following formula returns TRUE since 2 is equal to 2.
UPPER
The UPPER function converts text to uppercase.
Syntax
Parameters
"text"
The text, column or mocker you want to convert to uppercase. This can be a reference or a text string.
Return value
The text that is converted to uppercase.
Example
The following formula returns the text that is converted to uppercase which is "HELLO WORLD!". Or use UPPER to convert all addressess to uppercase.
VAR
The VAR function calculates the variance based on a sample of a population.
VARassumes that its arguments represent a sample of the population. If your data represents the entire population, use theVARPfunction instead.Arguments can be numbers, names, arrays, or values that contain numbers.
Logical values and text representations of numbers entered directly into the argument list are included in the calculation.
If an argument is an array, only the numbers within that array are considered; empty values, logical values, text, or error values in the array are ignored.
Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error.
If you want to include logical values and text representations of numbers in the calculation, use the
VARAfunction.
Syntax
Parameters
value1
The first number, column or mocker corresponding to a sample of a population.
value2, ... value30
Additional arguments corresponding to a sample of a population, up to 30 total arguments.
Return value
Variance of a sample.
Example
The following first formula calculates variance of the sample below which is 714.23. The second one calculates variance of given columns for every row.
VAR.P
The VAR.P function calculates the variance based on the entire population, ignoring logical values and text.
VAR.Passumes that its arguments represent the entire population. If your data represents only a sample of the population, use theVAR.Sfunction instead.Arguments can include numbers, names, arrays, or values that contain numbers.
Logical values and text representations of numbers entered directly into the argument list are included in the calculation.
If an argument is an array, only the numbers within that array are considered; empty values, logical values, text, or error values are ignored.
Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error.
If you want to include logical values and text representations of numbers in the calculation, use the
VARPAfunction.
Syntax
Parameters
value1
The first number, column or mocker corresponding to the population.
value1, value2, ... value30
Additional arguments corresponding to the population, up to 30 total arguments.
Return value
Variance of a population.
Example
The following formula calculates variance of the population below which is 642.81. The second one calculates variance of the population for given columns.
VAR.S
The VAR.S function estimates variance based on a sample, ignoring logical values and text.
VAR.Sassumes that its arguments represent a sample of the population. If your data represents the entire population, use theVAR.Pfunction instead.Arguments can include numbers, names, arrays, or values that contain numbers.
Logical values and text representations of numbers entered directly into the argument list are included in the calculation.
If an argument is an array, only the numbers within that array are considered; empty values, logical values, text, or error values are ignored.
Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error.
If you want to include logical values and text representations of numbers in the calculation, use the
VARAfunction.
Syntax
Parameters
value1
The first number, column or mocker corresponding to a sample of the population.
value2, ... value30
Additional arguments corresponding to a sample of the population, up to 30 total arguments.
Return value
Variance of a sample.
Example
The following formula calculates variance of the sample below which is 714.23. The second one calculates variance of the sample for given columns.
VARA
The VARA function estimates variance based on a sample, considering numbers, text representations of numbers, and logical values.
VARAassumes that its arguments represent a sample of the population. If your data represents the entire population, use theVARPAfunction instead.Arguments can include numbers, names, arrays, text representations of numbers, or logical values such as
TRUEandFALSE.Logical values and text representations of numbers entered directly into the argument list are included in the calculation.
Arguments containing
TRUEevaluate as 1.Arguments containing text or
FALSEevaluate as 0.
If an argument is an array, only the values within that array are considered. Empty values and text within the array are ignored.
Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error.
If you do not want to include logical values and text representations of numbers in the calculation, use the
VARfunction.
Syntax
Parameters
value1, value2, ... value30
value1 is required, and subsequent values are optional. You can provide 1 to 30 values corresponding to a sample of the population.
Return value
Variance of a sample.
Example
The following formula calculates variance of the sample below which is 714.23. The second one calculates variance of the sample for given columns.
VARP
The VARP function calculates the variance based on the entire population.
VARPassumes that its arguments represent the entire population. If your data represents a sample of the population, use theVARfunction instead.Arguments can include numbers, names, arrays, or values that contain numbers.
Logical values and text representations of numbers entered directly into the argument list are included in the calculation.
If an argument is an array, only the numbers within that array are considered; empty values, logical values, text, or error values in the array are ignored.
Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error.
If you want to include logical values and text representations of numbers in the calculation, use the
VARPAfunction.
Syntax
Parameters
value1
The first number, column or mocker corresponding to the population.
value2, ... value30
Additional arguments corresponding to the population, up to 30 total arguments.
Return value
Variance of a population.
Example
The following formula calculates variance of the population below which is 642.81. The second one calculates variance of the population for given columns.
WEEKNUM
The WEEKNUM function returns the week number of a specific date. For example, the week containing January 1 is considered the first week of the year and is numbered as week 1.
Syntax
Parameters
date
A date, column or mocker within the week you want to identify. Dates should be entered using the DATE function or as the result of other formulas or functions. Entering dates as text may cause errors.
[return_type]
A number that determines which day of the week the week starts on. The default value is 1 (where Sunday is the first day of the week).
Return value
The week number in the year.
Example
The following formulas calculate the week number in the year.
XOR
The XOR function returns a logical Exclusive Or (XOR) of all the provided arguments.
The arguments must evaluate to logical values such as
TRUEorFALSE, or be in arrays or references that contain logical values.If an array or reference argument contains text or empty cells, those values are ignored.
If the specified range contains no logical values,
XORreturns the#VALUE!error.You can use an XOR array formula to check if a value occurs in an array.
The result of
XORisTRUEwhen the number ofTRUEinputs is odd andFALSEwhen the number ofTRUEinputs is even.
Syntax
Parameters
Logicalvalue1, Logicalvalue2 ... Logicalvalue30
Logical1 is required, and subsequent logical values are optional. You can provide 1 to 30 conditions to test, which can be either TRUE or FALSE, and can include logical values, arrays, or references.
Return value
TRUE if an odd number of arguments evaluates to TRUE.
Example
The following formula returns TRUE if odd number of arguments (only one or 3) evaluates to TRUE, otherwise, FALSE.
YEAR
The YEAR function returns the year corresponding to a given date as an integer between 1900 and 9999. The values returned by the YEAR, MONTH, and DAY functions will be Gregorian values, regardless of the display format for the supplied date.
Syntax
Parameters
number
The date, column or mocker for which you want to extract the year. Dates should be entered using the DATE function or as the result of other formulas or functions. Entering dates as text may cause errors.
Return value
The year as a number according to the internal calculation rules.
Example
The following first formula returns 2012 as a number according to the internal calculation rules. The second formula will return only year from the birth date column.
Info: The below functions are planned to be introduced in the future releases of Syntho.
Planned Functions for future releases
STDEV
Returns standard deviation of a sample. When used with column names, the value is calculated anew for each row.
STDEV(Value1, Value2, ... Value30)
STDEV.P
Returns standard deviation of a population. When used with column names, the value is calculated anew for each row.
STDEV.P(Value1, Value2, ... Value30)
STDEV.S
Returns standard deviation of a sample. When used with column names, the value is calculated anew for each row.
STDEV.S(Value1, Value2, ... Value30)
STDEVA
Returns standard deviation of a sample. When used with column names, the value is calculated anew for each row.
STDEVA(Value1, Value2, ... Value30)
STDEVP
Returns standard deviation of a population. When used with column names, the value is calculated anew for each row.
STDEVP(Value1, Value2, ... Value30)
STDEVPA
Returns standard deviation of a population. When used with column names, the value is calculated anew for each row.
STDEVPA(Value1, Value2, ... Value30)
MAX
Returns the maximum value in a list of arguments. When used with column names, the value is calculated anew for each row.
MAX(Number1, Number2, ... Number30)
MAXA
Returns the maximum value in a list of arguments. When used with column names, the value is calculated anew for each row.
MAXA(Value1, Value2, ... Value30)
MEDIAN
Returns the median of a set of numbers. When used with column names, the value is calculated anew for each row.
MEDIAN(Number1, Number2, ... Number30)
MIN
Returns the minimum value in a list of arguments. When used with column names, the value is calculated anew for each row.
MIN(Number1, Number2, ... Number30)
MINA
Returns the minimum value in a list of arguments. When used with column names, the value is calculated anew for each row.
MINA(Value1, Value2, ... Value30)
COUNT
Counts how many numbers are in the list of arguments.
COUNT(Value1, Value2, ... Value30)
COUNTA
Counts how many values are in the list of arguments.
COUNTA(Value1, Value2, ... Value30)
COUNTBLANK
Returns the number of empty cells.
COUNTBLANK(Range)
IFNA
Returns the value if the cell does not contain the #N/A (value not available) error value, or the alternative value if it does.
IFNA(Value, Alternate_value)
Last updated
