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:

1. Data Cleaning and Transformation: Effortlessly clean and reformat data, such as trimming whitespace, changing text casing, or converting date formats.

2. Statistical Analysis: Perform statistical calculations like averages, variances, or standard deviations to derive insights from numerical data sets.

3. Logical Operations: Apply logical tests to data to create flags, indicators, or to filter and categorize data based on specific criteria.

4. Mathematical Operations: Execute a variety of mathematical operations, enabling complex calculations like financial modelling or engineering calculations.

5. 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.

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:

1. Formula Structure: An example Calculated Column formula is:SUM(Table[ExistingColumn]).

2. 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.

3. Functions: Calculated Column includes a wide range of functions that can be used in your expressions, such as aggregation functions (SUM, AVERAGE), time intelligence functions (DATE, DATEADD), logical functions (IF, AND, OR), and many more. Functions follow the syntax of FUNCTION_NAME(arguments).

4. 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.

5. Operators: Calculated Column supports various operators for arithmetic calculations (+, -, *, /), and comparison (<, >, <=, >=, =, <>) operations.

6. Constants: You can use constants like numbers (123), strings ("text"), and dates (DATE(2020, 12, 31)).

7. Handling Errors: Use error handling functions like IFERROR to manage errors in your expressions.

8. 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:

[Total Sales] + ([Total Sales] * [Tax Rate])

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([Gender] = 'M', MOCK_FIRST_NAME, IF([Gender] = 'F', MOCK_LAST_NAME_FEMALE, 'nothing'))

Using calculated column feature

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_NAME predefined mocker has a variant MOCK_CONSISTENT_FIRST_NAME. This new mocker consistently maps and mocks input from the same column where it is attached.

Consistent Mapping with Calculated Columns

Supported calculated column functions

ABS

Returns the absolute value of a number.

Syntax

ABS(number)

Parameters

Parameter	Description
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.

ABS([HEALTHCARE_COVERAGE])

ABS(-5)

ABS(MOCK_RANDOM_DIGIT)

ACOS

Returns the arccosine (inverse cosine) of a given number. The arccosine represents the angle whose cosine corresponds to the provided number. The result is an angle measured in radians, within the range of 0 to π.

Syntax

ACOS(number)

Parameters

Parameter	Description
number	The cosine value of the desired angle, column or mock value, which must fall between -1 and 1.

Return value

The inverse trigonometric cosine of the number provided.

Example

The following formulas calculate arccosine (inverse cosine) of a given column, number or a random value. Note that, to generate a random real number between -1 and 1, use to following formula with RAND function.

ACOS([HEALTHCARE_COVERAGE])

ACOS(0.7)

ACOS(1 + (-2) * RAND())

ACOSH

Computes the inverse hyperbolic cosine of a specified number. The input number must be 1 or greater. The inverse hyperbolic cosine is the value for which the hyperbolic cosine equals the given number, meaning that ACOSH(COSH(number)) will return the original number.

Syntax

ACOSH(number)

Parameters

Parameter	Description
number	Any real number column that is 1 or greater.

Return value

An inverse hyperbolic cosine of a specified number.

Example

The following formula calculates inverse hyperbolic cosine of the values of the column called "HEALTHCARE_EXPENSES".

ACOSH([HEALTHCARE_EXPENSES])

ACOSH(-5)

ACOSH(MOCK_RANDOM_DIGIT)

ACOT

Returns the principal value of the arccotangent (inverse cotangent) of a given number. The result is an angle measured in radians, ranging from 0 to π. To convert the result from radians to degrees, you can use the DEGREES function or multiply by 180/PI().

Syntax

ACOT(number)

Parameters

Parameter	Description
number	The cotangent of the desired angle, column or mock value. This must be a real number.

Return value

The principal value of the arccotangent (inverse cotangent) of a given number.

Example

The following formulas calculate the principal value of the arccotangent (inverse cotangent) of the values of the column called "HEALTHCARE_EXPENSES", number 7 and mock value.

ACOT([HEALTHCARE_EXPENSES])

ACOT(7)

ACOT(MOCK_RANDOM_DIGIT)

ACOTH

Returns the inverse hyperbolic cotangent of a number.

Syntax

ACOTH(number)

Parameters

Parameter	Description
number	The absolute value, column or mock value of the input must be greater than 1.

Return value

The inverse hyperbolic cotangent of a specified number.

Example

The following formulas calculate the inverse hyperbolic cotangent of given column, mock value and number 20 which is approximately 0.05.

ACOTH(20)

ACOTH([COTANGENT])

ACOTH(MOCK_RANDOM_DIGIT)

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

AND(Logicalvalue1, Logicalvalue2, ...Logicalvalue30)

Parameters

Parameter	Description
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.

AND([GENDER] = 'M', [HEALTHCARE_COVERAGE] > 100)

ARABIC

Converts a Roman numeral into an Arabic numeral.

Syntax

ARABIC(string)

Parameters

Parameter	Description
string	A string, column or mock value representing a Roman numeral, enclosed in single or double quotation marks.

Return value

An Arabic numeral.

Example

The following formulas convert the given column or a roman numeral "MCMXCVII" into an Arabic numeral which is 1997.

ARABIC([ROMAN_NUMERALS])

ARABIC("MCMXCVII")

ASIN

Calculates the arcsine (inverse sine) of a specified number. The arcsine represents the angle whose sine is the given number. The result is an angle measured in radians, ranging from -π/2 to π/2. To convert the result from radians to degrees, multiply the output by 180/PI() or use the DEGREES function.

Syntax

ASIN(number)

Parameters

Parameter	Description
number	The sine of the angle, column or number you want and must be from -1 to 1.

Return value

The inverse trigonometric sine of a number.

Example

The following formulas calculate the inverse trigonometric sine of 0.7, given column or mock value.

ASIN(0.7)

ASIN([ANGLES])

ASIN(1 + (-2) * RAND())

ASINH

Calculates the inverse hyperbolic sine of a specified number. The inverse hyperbolic sine is the value for which the hyperbolic sine equals the given number, meaning ASINH(SINH(number)) will return the original number.

Syntax

ASINH(number)

Parameters

Parameter	Description
number	Any real number, column or mock value.

Return value

The inverse hyperbolic sine of a number.

Example

The following formulas calculate the inverse hyperbolic sine of the values of the column called "HEALTHCARE_EXPENSES", number or mock values.

ASINH([HEALTHCARE_EXPENSES])

ASINH(-5)

ASINH(MOCK_RANDOM_DIGIT)

ATAN

Calculates the arctangent (inverse tangent) of a specified number. The arctangent represents the angle whose tangent equals the given number. The result is an angle measured in radians, ranging from -π/2 to π/2. To convert the result from radians to degrees, multiply the output by 180/PI() or use the DEGREES function.

Syntax

ATAN(number)

Parameters

Parameter	Description
number	The tangent of a number, column or mock value of the desired angle.

Return value

Returns the inverse trigonometric tangent of a number.

Example

The following formulas calculate the inverse trigonometric tangent of the values of the column called "HEALTHCARE_EXPENSES", number or mock value.

ATAN([ANGLES])

ATAN(-5)

ATAN(MOCK_RANDOM_DIGIT)

ATAN2

Calculates the arctangent (inverse tangent) of the specified x- and y-coordinates. The arctangent represents the angle between the x-axis and a line that passes through the origin (0, 0) and a point with coordinates (x_num, y_num). The angle is measured in radians, ranging from -π to π, excluding -π.

Syntax

ATAN2(numberX, numberY)

Parameters

Parameter	Description
numberX	The x-coordinate of the point.
numberY	The y-coordinate of the point.

Return value

The inverse trigonometric tangent of the specified x and y coordinates.

Example

The following formulas calculate the inverse trigonometric tangent of x (latitudes) and y (longitudes) coordinates.

ATAN2([LAT], [LON])

ATAN2(-5, 3)

ATAN2(MOCK_RANDOM_DIGIT)

ATANH

Calculates the inverse hyperbolic tangent of a specified number. The input number must be between -1 and 1 (excluding -1 and 1). The inverse hyperbolic tangent is the value for which the hyperbolic tangent equals the given number, meaning ATANH(TANH(number)) will return the original number.

Syntax

ATANH(number)

Parameters

Parameter	Description
number	Any real number column between -1 and 1, but not including -1 and 1.

Return value

The inverse hyperbolic tangent of a number.

Example

The following formulas calculate the inverse hyperbolic tangent of -0.5, given column called "ANGLES" or random number. Note that, to generate a random real number between -1 and 1, use to following formula with RAND function.

ATANH(-0.5)  

ATANH([ANGLES])

ATANH(1 + (-2) * RAND())

AVEDEV

Calculates the average of the absolute deviations of data points from their mean. AVEDEV provides a measure of the variability within a data set.

Syntax

AVEDEV(number1, number2, ...number30)

Parameters

Parameter	Description
number1, number2, ...number30	"number1" is required, and subsequent numbers are optional. You can provide 1 to 30 arguments to calculate the average of the absolute deviations. Arguments can be columns, numbers or mock values.

Return value

The average deviation of the arguments.

Example

The following formulas calculate the average deviation of the values of the columns "HEALTHCARE_EXPENSES" and "HEALTHCARE_COVERAGE" for every row or numbers.

AVEDEV([HEALTHCARE_EXPENSES], [HEALTHCARE_COVERAGE])

AVEDEV(1, 3, 5, 7)

AVERAGE

Calculates the average (arithmetic mean) of the provided arguments/numbers.

Syntax

AVERAGE(number1, number2, ...number30)

Parameters

Parameter	Description
number1	The first number column, or range for which you want to calculate the average.
number2, ...number30	Additional numbers, or column for which you want to calculate the average, up to a maximum of 30.

Return value

The average of the arguments.

Example

The following formula calculates the average of the values of "HEALTHCARE_EXPENSES" and "HEALTHCARE_COVERAGE" for every row, numbers or mock values.

AVERAGE([HEALTHCARE_EXPENSES], [HEALTHCARE_COVERAGE])

AVERAGE(1, 3, 5, 7)

AVERAGE(MOCK_RANDOM_DIGIT, MOCK_RANDOM_DIGIT, MOCK_RANDOM_DIGIT)

AVERAGEA

Calculates the average (arithmetic mean) of the values in the list of arguments. Arguments can be numbers; names, or references that contain numbers; text representations of numbers; or logical values like TRUE and FALSE. If you want to exclude logical values and text representations of numbers in a reference from the calculation, use the AVERAGE function instead.

Syntax

AVERAGEA(number1, number2, ...number30)

Parameters

Parameter	Description
number1, number2, ...number30	"column1" is required, and subsequent values are optional. You can provide 1 to 30 values for which you want to calculate the average.

Return value

The average of the arguments.

Example

The following formula calculates the average of the below columns for every row.

AVERAGEA([AMOUNT_COVERED], [REVENUE], [COVERED_ENCOUNTERS], [COVERED_PROCEDURES], [COVERED_MEDICATIONS])

AVERAGEA(1, 3, 5, 7)

AVERAGEA(MOCK_RANDOM_DIGIT, MOCK_RANDOM_DIGIT, MOCK_RANDOM_DIGIT)

AVERAGEIF

Returns a column of dates, shifted forward or backward by the specified number of intervals.

Syntax

AVERAGEIF(Range, Criterion, Average_Range)

Parameters

Parameter	Description
Range	One or more arguments to average, including numbers, names, mock values, or columns containing numbers.
Criteria	The condition that defines which arguments to average. This can be a number, expression, or text.
Average_range	The actual set of arguments to average. If omitted, the range is used.

Return value

The arithmetic mean of all arguments in a range that satisfy a given condition.

Example

The following formula calculates the arithmetic mean of all incomes in a range that satisfy a given conditions which are either general or family practices.

AVERAGEIF([SPECIALITY],"=*PRACTICE",[INCOME])

BASE

Converts a number into its text representation in the specified radix (base).

Syntax

BASE(number, Radix, MinimumLength)

Parameters

Parameter	Description
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).

1. BASE(5, 2) converts the decimal number 5 to base 2 (binary). Result is 101.

BASE(5, 2)

2. 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(5, 2, 8)

3. BASE(42,16) converts 42 to base 16 (hexadecimal). The result is 2A.

BASE(42,16)

Alternatively, use columns or mock values.

BASE([NUMBER], [RADIX])

BASE(MOCK_RANDOM_DIGIT, MOCK_RANDOM_DIGIT)

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

CEILING(number, Significance)

Parameters

Parameter	Description
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.

Formula	Description	Result
CEILING(3.1, 1)	Rounds 3.3 up to nearest multiple of 1	4
CEILING(-4.5, -2)	Rounds -4.5 up to nearest multiple of -2	-6
CEILING(-2.5, 2)	Rounds -2.5 up to nearest multiple of 2	-2
CEILING(5.5, 0.1)	Rounds 5.5 up to nearest multiple of 0.1	5.5
CEILING(0.9345, 0.001)	Rounds 0.9345 up to nearest multiple of 0.001	0.935
CEILING([N], [Significance])	Rounds column N up to nearest multiple of column Significance.	null

CEILING.MATH

Rounds a number up to the nearest integer or to the nearest specified multiple of significance. By default, the significance is +1 for positive numbers and -1 for negative numbers. Positive numbers with decimal portions are typically rounded up to the nearest integer. Negative numbers with decimal portions are rounded up toward zero by default. You can modify the rounding direction for negative numbers by using the significance and mode arguments. For example, rounding -2.5 with a significance of 1 and a mode of 1 will round away from zero to -3. Various combinations of significance and mode can alter the rounding behavior of negative numbers. Note that the mode argument does not influence positive numbers and the significance argument rounds the number up to the nearest integer that is a multiple of the specified significance unless the number is already an integer. For example, with a significance of 5, the number is rounded up to the next integer multiple of 5. Lastly, if dividing the number by a significance of 2 or more results in a remainder, the number is rounded up.

Syntax

CEILING.MATH(number, Significance, Mode)

Parameters

Parameter	Description
number	The number, column, mock value to be rounded, which must be between -2.229E-308 and 9.99E+307.
significance	The multiple to which the number column will be rounded.
mode	Determines whether a negative number is rounded toward or away from zero.

Return value

A number rounded up to the nearest integer or to the nearest specified multiple of significance.

Example

The following formulas round -6.5 down (away from 0) to the nearest integer that is a multiple of 3 with a mode of -1, which reverses rounding direction (-9). Use columns or mock values to round numbers as well.

CEILING.MATH(-6.5,3,-1)

CEILING.MATH([NUMBERS],3,-1)

CEILING.MATH(MOCK_RANDOM_DIGIT,3,-1)

CEILING.PRECISE

Rounds a number up to the nearest integer or the nearest specified multiple of significance. The number is always rounded up, regardless of its sign. If either the number or the significance is zero, the function returns zero. The function uses the absolute value of the significance, so it rounds the number up regardless of the signs of the number and significance.

Syntax

CEILING.PRECISE(number, significance)

Parameters

Parameter	Description
number	The column, number or mock values to be rounded.
significance	The multiple to which the number will be rounded. If omitted, the default value is 1.

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.

Formula	Description	Result
CEILING.PRECISE(6.4)	Rounds 6.4 up to nearest multiple of 1	4
CEILING.PRECISE(-6.4)	Rounds -6.4 up to nearest multiple of 1 and it rounds toward 0 because the number is negative.	-6
CEILING.PRECISE(2.5, 2)	Rounds 2.5 up to nearest multiple of 2	4
CEILING.PRECISE(2.5, -2)	Rounds 2.5 up to nearest multiple of -2	4
CEILING.PRECISE(-2.5, 2)	Rounds -2.5 up to nearest multiple of 2	-2
CEILING.PRECISE(-2.5, -2)	Rounds -2.5 up to nearest multiple of -2	-4
CEILING.PRECISE([N], -2)	Rounds N column up to nearest multiple of -2	null

CHAR

Returns the character corresponding to a specified number.

Syntax

CHAR(number)

Parameters

Parameter	Description
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.

CHAR(77)

CHAR([NUMBERS])

CHAR(MOCK_RANDOM_DIGIT)

CHOOSE

Returns a value from a list of arguments based on a specified index number. The CHOOSE function allows you to select one of up to 30 values using the index number. For instance, if value1 through value7 represent the days of the week, CHOOSE can return a specific day when provided with an index number between 1 and 7.

Syntax

CHOOSE(Index, number1, number2 ...number30)

Parameters

Parameter	Description
index	Returns a value from a list of arguments based on a specified index number. The CHOOSE function allows you to select one of up to 30 values using the index number. For instance, if number1 through number5 represent the days of the week, CHOOSE can return a specific day when provided with an index number between 1 and 5. If index_num is 1, CHOOSE returns value1; if 2, value2; and so on. If index_num is a fraction, it is truncated to the nearest lower integer before use.
number1, number2 ...number30	column1, column2, ...: number1 is required, and subsequent values are optional. You can provide 1 to 254 values from which CHOOSE will select based on index_num. These arguments can include numbers, defined names, formulas, functions, or text.

Return value

A value from a list of up to 30 values.

Example

The following formula returns the value of the third list argument which is ZIP. You can use numbers or mock values as well.

CHOOSE(3, [CITY], [STATE], [ZIP], [LAT], [LON])

CHOOSE(2, 7, 3, -5, 8)

CHOOSE(1, MOCK_RANDOM_DIGIT, MOCK_RANDOM_DIGIT)

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

CLEAN(number)

Parameters

Parameter	Description
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.

CLEAN(CHAR(3)&"Patient Medications"&CHAR(17))

CLEAN(CHAR(3)&[description]&CHAR(17))

CLEAN(CHAR(3)&MOCK_FIRST_NAME&CHAR(17))

CODE

Returns the numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer.

Syntax

CODE(number)

Parameters

Parameter	Description
number	The text, column or mock values from which you want to obtain the code of the first character.

Return value

A numeric code for the first character in a text string.

Example

The following formula calculates the text from which you want to obtain the code of the first character which is either 70 or 77, since they correspond to either "F" or "M". Alternatively, use text or mock values.

CODE([GENDER])

CODE("A")

CODE(MOCK_RANDOM_LETTER)

COMPLEX

Converts real and imaginary coefficients into a complex number in the form of x + yi or x + yj.

Syntax

COMPLEX(Re, Im, Symbol)

Parameters

Parameter	Description
Re	The real coefficient of the complex number, column or mokc values.
Im	The imaginary coefficient of the complex number, column or mock values.
Symbol	The suffix for the imaginary part of the complex number. If omitted, the default suffix is "j".

Important

All complex number functions accept "i" and "j" as suffixes, but not "I" or "J".

When working with multiple complex numbers in functions, all suffixes must match.

Return value

The complex number from Re and Im parts.

Example

The following formula calculates the complex number from Re and Im parts which is LAT + LON * j, for example, (2+5j). Alternatively, use numbers or mock values.

COMPLEX([LAT],[LON])

COMPLEX(2, 5)

COMPLEX(MOCK_RANDOM_DIGIT, MOCK_RANDOM_DIGIT)

CONCATENATE

The CONCATENATE function joins two or more text strings into a single string.

Syntax

CONCATENATE("Text1", ..., "Text30")

Parameters

Parameter	Description
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.

CONCATENATE([STATE], ", ", [COUNTY])

CONCATENATE(MOCK_FIRST_NAME_FEMALE, " ", MOCK_LAST_NAME_FEMALE)

CONCATENATE("Brittany", " ", "Jenkins")

COS

Calculates the cosine of a specified angle. If the angle is in degrees, convert it to radians by multiplying it by PI()/180 or by using the RADIANS function.

Syntax

COS(number)

Parameters

Parameter	Description
number	The angle in radians, column or mock values for which you want to find the cosine.

Return value

The cosine of the given angle (in radians).

Example

The following formulas calculate the cosine of the given angle (in radians), numbers or mock values.

COS([LAT])

COS(30)

COS(MOCK_RANDOM_DIGIT)

COSH

Calculates the hyperbolic cosine of a given number.

Syntax

COSH(number)

Parameters

Parameter	Description
number	Any real number, column or mock value for which you want to find the hyperbolic cosine.

Return value

The hyperbolic cosine of the given value.

Example

The following formulas calculate the hyperbolic cosine of latitudes of every row, number or mock values.

COSH([LAT])

COSH(30)

COSH(MOCK_RANDOM_DIGIT)

COT

Calculates the cotangent of a given angle in radians.

Syntax

COT(number)

Parameters

Parameter	Description
number	The angle in radians, column or mock values for which you want to find the cotangent.

Return value

The cotangent of the given angle (in radians).

Example

The following formula calculates the cotangent of the given angle (in radians), mock value or a column such as healthcare coverage.

COT([HEALTHCARE_COVERAGE])

COT(30)

COT(MOCK_RANDOM_DIGIT)

COUNT

The COUNT function tallies the number of arguments containing numeric values and counts numbers within the list of provided arguments. Use COUNT to determine the number of numeric entries in a range or array. The arguments can include various types of data, but only numeric values are counted.

Note that:

• Arguments that are numbers, dates, or text representations of numbers (e.g., a number in quotation marks like "1") are counted.

• Logical values and text representations of numbers directly typed into the arguments are also counted.

• Arguments that are error values or text that cannot be converted to numbers are not counted.

• If an argument is an array or reference, only the numeric values within that array or reference are counted; logical values, text, or error values are ignored.

• To count logical values, text, or error values, use the COUNTA function.

• To count only numbers that meet specific criteria, use the COUNTIF or COUNTIFS functions.

Syntax

COUNT(number1, number2 ...number30)

Parameters

Parameter	Description
number1	The first item or range in which you want to count numbers, columns or mock values.
number2 ...number30	Up to 30 additional items or ranges in which you want to count numbers, columns or mock values.

Return value

Count of how many numbers are in the list of arguments.

Example

The following formulas calculate counts of how many values are in the list of columns for every row, numbers or mock values.

COUNT({[FIRSTNAME], [MAIDENNAME], [LASTNAME], [MARITAL]})

COUNT(30, 40, 45, 25)

COUNT(MOCK_RANDOM_DIGIT, MOCK_RANDOM_DIGIT, MOCK_RANDOM_DIGIT)

COUNTA

The COUNTA function counts the number of non-empty arguments in a specified range. The COUNTA function counts arguments that contain any type of data, including error values and empty text (""). If you only need to count cells that contain numbers, use the COUNT function instead. To count arguments that meet specific criteria, use the COUNTIF function.

Syntax

COUNTA(number1, number2 ...number30)

Parameters

Parameter	Description
number1	The first argument representing the numbers, columns or mock values you want to count.
number2 ...number30	Additional arguments representing the values to count, up to a maximum of 30 arguments.

Return value

Count of how many values are in the list of arguments.

Example

The following formula calculates counts of how many values are in the list of columns that is non-blank for every row, numbers or mock values.

COUNTA({[FIRSTNAME], [MAIDENNAME], [LASTNAME], [MARITAL]})

COUNTA(30, 40, 45, 25)

COUNTA(MOCK_RANDOM_DIGIT, MOCK_RANDOM_DIGIT, MOCK_RANDOM_DIGIT)

COUNTBLANK

The COUNTBLANK function, counts the number of empty values within a specified range.

Syntax

COUNTBLANK(number1, number2 ...number30)

Parameters

Parameter	Description
number1, number2 ...number30	The column range where you want to count the blank values.

Return value

A column of date values.

Example

The following formulas calculate counts of how many values are in the list of columns that is blank for every row, number or mock values.

COUNTBLANK({[FIRSTNAME], [MAIDENNAME], [LASTNAME], [MARITAL]})

COUNTBLANK(30, 40, , 25)

COUNTBLANK(MOCK_RANDOM_DIGIT, , MOCK_RANDOM_DIGIT)

COUNTIF

The COUNTIF function counts the number of values that meet a specific criterion. Use COUNTIF to count how many times a particular thing appears in a range.

Syntax

COUNTIF(number1, number2 ...number30, Criteria)

Parameters

Parameter	Description
number	The number, column or mock values you want to evaluate.
criteria	The condition that determines which values to count.

Return value

The number of values that meet with certain criteria within a value range.

Example

The following formulas calculate the number of names that is "David" within first name and last name columns for every row.

COUNTIF({[FIRSTNAME], [LASTNAME]}, "David")

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

DATE(year, month, day)

Parameters

Parameter	Description
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.

DATE(2012, 8, 24)

DATE("2012", "8", "24")

DATE([year], [month], [day])

DATE(MOCK_YEAR, MOCK_MONTH, 4)

DATEADD

Returns a column of dates, shifted forward or backward by the specified number of intervals.

Syntax

DATEADD(dates, number_of_intervals, interval)

Parameters

Parameter	Description
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.

DATEADD([Birthdate], 20, year)  

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

DATEVALUE(date_text)

Parameters

Parameter	Description
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.

DATEVALUE([BIRTHDATE])

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

DAY(number)

Parameters

Parameter	Description
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.

DAY("24/6/2012")

DAY([BIRTHDATE])

DAYS

The DAYS function calculates the number of days between two dates.

Syntax

DAYS(date2, date1)

Parameters

Parameter	Description
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.

DAYS("24/6/2012","14/6/2012")

DATEDIFF

The DateDiff function returns the number of time intervals between two specified dates.

Syntax

DATEDIFF(date1, date2, interval)

Parameters

Parameter	Description
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.

Return value

The number of units between two dates as defined in the interval.

Example

The following formula calculates the number of units between two dates as defined in the interval which is

DATEDIFF([EXPIRY_DATE], [INITIAL_DATE], interval)

DEC2HEX

The DEC2HEX function converts a decimal number to its hexadecimal equivalent.

Syntax

DEC2HEX(number, places)

Parameters

Parameter	Description
number	The decimal integer, column or mock integer to convert. If the number is negative, the places argument is ignored, and DEC2HEX returns a 10-character (40-bit) hexadecimal number where the most significant bit represents the sign and the remaining 39 bits represent the magnitude. Negative numbers are represented using two's-complement notation.
places	Specifies the number of characters to use in the output. If omitted, DEC2HEX returns the minimum number of characters needed. This argument is useful for padding the result with leading zeros.

Return value

The hexadecimal number for the decimal number entered.

Example

The following formula converts decimal value 250 to hexadecimal with 5 characters ("padded" with three leading zeros) which is 000FA.

DEC2HEX(250, 5)

DEC2HEX([COST], 5)

DECIMAL

The DECIMAL function converts a text representation of a number in a specified base (radix) into a decimal number.

Syntax

DECIMAL("text", radix)

Parameters

Parameter	Description
text	The text, column or mock text representation of the number to be converted.
radix	The base of the number to be converted. Must be an integer.

Return value

A text with characters converted from a number system to a positive integer in the base radix given.

Example

The following formula converts text "FA" from 16 base number system to a positive integer in the base radix given which is 250.

DECIMAL("FA",16)

DEGREES

Returns a column of dates, shifted forward or backward by the specified number of intervals.

Syntax

DEGREES(number)

Parameters

Parameter	Description
number	The angle in radians, column or mock value that you want to convert.

Return value

Degrees converted from radians.

Example

The following formula converts PI radians into degrees which returns 180.

DEGREES(PI())

DEGREES([COST])

DELTA

The DELTA function checks whether two values are equal. It returns 1 if number1 equals number2, and 0 otherwise.

Syntax

DELTA(number_1, number_2)

Parameters

Parameter	Description
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.

DELTA([HEALTHCARE_COVERAGE], [HEALTHCARE_EXPENSES])

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

EVEN(number)

Parameters

Parameter	Description
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.

EVEN([HEALTHCARE_COVERAGE])

EXP

The EXP function returns the value of e raised to the power of a specified number, where e is the mathematical constant approximately equal to 2.71828182845904, the base of the natural logarithm. To calculate powers of other bases, use the exponentiation operator (^). The EXP function is the inverse of the LN function, which calculates the natural logarithm of a number.

Syntax

EXP(number)

Parameters

Parameter	Description
number	The exponent, number, column or mocker to which the base e is raised.

Return value

Returns constant e raised to the power of a number.

Example

The following formula calculates constant e raised to the power of either 12 which is 162754.79141900383 or the values of "DISPENSES".

EXP(12)

EXP([DISPENSES])

FACT

Returns a factorial of a number.

Syntax

FACT(number)

Parameters

Parameter	Description
number	The nonnegative number, column or mocker for which you want to calculate the factorial. If the number is not an integer, it will be truncated to its integer value before the factorial is computed.

Return value

A factorial of a number.

Example

The following formula calculates a factorial of either 5 which is 120 or the values of "DISPENSES".

FACT([DISPENSES])

FACTDOUBLE

The FACTDOUBLE function returns the double factorial of a specified number.

Syntax

FACTDOUBLE(number)

Parameters

Parameter	Description
number	The number, column or mocker for which the double factorial is calculated. If the number is not an integer, it is truncated.

Return value

A double factorial of a number.

Example

The following formula calculates a double factorial of 6 which is 48. If it would be 5 then FACTDOUBLE(6) would return 15. It is the product of all the positive integers up to the n that have the same parity (odd or even) as the n. For example, if n=6, then 2*4*6*8 = 48 or if n=5, then 1*3*5=15.

FACTDOUBLE(6)

FACTDOUBLE([DISPENSES])

FALSE

Returns the logical value FALSE.

Syntax

FALSE()

Return value

The logical value FALSE.

Example

The following formula returns the logical value FALSE.

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 number is positive and the significance is negative, the function returns an error message.

Syntax

FLOOR(number, significance)

Parameters

Parameter	Description
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.

Formula	Description	Result
FLOOR(7.7,3)	Rounds 7.7 down to nearest multiple of 3.	6
FLOOR(-3.4,-3)	Rounds -3.4 down to nearest multiple of -3.	-3
FLOOR(1.453,0.01)	Rounds 1.453 down to nearest multiple of 0.01.	1.45

FLOOR.MATH

The FLOOR.MATH function rounds a number down to the nearest integer or the nearest multiple of a specified significance.

By default, positive numbers with decimal portions are rounded down to the nearest integer and negative numbers with decimal portions are rounded away from zero by default.

Modify the rounding direction for negative numbers by using 0 or a negative value as the mode. For example, rounding -5.25 with a significance of 1 and a mode of -1 rounds toward zero, resulting in -5.

The significance argument rounds the number down to the nearest integer that is a multiple of the specified significance, unless the number is already an integer. Finally, if dividing the number by a significance of 2 or greater results in a remainder, the number is rounded down.

Syntax

FLOOR.MATH(number, significance, mode)

Parameters

Parameter	Description
number	The number, column or mocker to be rounded down.
significance	The multiple to which you want to round the number. The default is 1.
mode	Determines the direction to round negative numbers—either toward or away from zero.

Return value

Rounds a number down to the nearest multiple of Significance.

Example

The following formulas rounds a number down to the nearest multiple of Significance.

Formula	Description	Result
FLOOR.MATH(19.5,4)	Rounds 19.5 down to the nearest integer that is a multiple of 4 (16).	16
FLOOR.MATH(-12.99,2)	Rounds -12.99 down (away from 0) to the nearest integer that is a multiple of 2 (-14).	-14
FLOOR.MATH(-4.7,2,-1)	Rounds -4.7 toward 0 to the nearest integer that is a multiple of 2, using a non-zero Mode, which reverses rounding direction (-4).	-4

FLOOR.PRECISE

The FLOOR.PRECISE function rounds a number down to the nearest integer or the nearest multiple of a specified significance. The number is always rounded down, regardless of its sign. If either the number or the significance is zero, the function returns zero. The function uses the absolute value of the significance, ensuring that the rounding is performed regardless of the signs of the number and significance.

Syntax

FLOOR.PRECISE(number, significance)

Parameters

Parameter	Description
number	The number, column or mocker to be rounded down.
significance	The multiple to which the number column will be rounded. If omitted, the default value is 1.

Return value

Rounds a number down to the nearest multiple of Significance.

Example

The following formula rounds a number down to the nearest multiple of Significance.

Formula	Description	Result
FLOOR.PRECISE(6.4)	Rounds 6.4 down to nearest multiple of 1.	6
FLOOR.PRECISE(-6.4, -1)	Rounds -6.4 down to nearest multiple of -1.	-6
FLOOR.PRECISE(6.4, 1)	Rounds 6.4 down to nearest multiple of 1.	6
FLOOR.PRECISE(-6.4, 1)	Rounds -6.4 down to nearest multiple of 1.	-7
FLOOR.PRECISE(6.4, -1)	Rounds 6.4 down to nearest multiple of -1.	6

GEOMEAN

The GEOMEAN function calculates the geometric mean of an array or range of positive numbers. Arguments can be numbers, arrays, or names that contain numbers. Logical values and text representations of numbers that are directly entered into the arguments are included in the calculation. If an array contains text, logical values, or empty cells, these are ignored. However, a value of zero are included in the calculation. Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error as well as if any data point is less than or equal to zero, GEOMEAN returns an error.

Syntax

GEOMEAN(number1, number2, ...number30)

Parameters

Parameter	Description
number1, number2, ...number30	number1 is required, while subsequent numbers are optional. You can provide 1 to 30 values for which you want to calculate the geometric mean.

Return value

Returns the geometric average.

Example

The following formula calculates the geometric average of below columns. Note that one of the columns includes negative numbers, therefore, use ABS() function to return absolute values of the column.

GEOMEAN([LAT], ABS([LON]), [HEALTHCARE_COVERAGE], [HEALTHCARE_EXPENSES])

HARMEAN

The HARMEAN function calculates the harmonic mean of a data set, which is the reciprocal of the arithmetic mean of the reciprocals of the data points. The harmonic mean is always less than the geometric mean, which in turn is always less than the arithmetic mean. Arguments can be numbers or names that contain numbers. Logical values and text representations of numbers that are directly entered into the arguments are included in the calculation. If an array or reference contains text, logical values, or empty values, these are ignored. However, arguments or values with a value of zero are included in the calculation. Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error. If any data point is less than or equal to zero, HARMEAN returns an error.

Syntax

HARMEAN(number1, number2, ...number30)

Parameters

Parameter	Description
number1, number2, ...number30	column1 is required, while subsequent numbers or columns are optional. You can provide 1 to 30 values for which you want to calculate the harmonic mean.

Return value

Returns the harmonic average.

Example

The following formula returns the harmonic average of the below columns for every row.

HARMEAN([LAT], ABS([LON]), [HEALTHCARE_COVERAGE], [HEALTHCARE_EXPENSES])

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

HEX2DEC(number)

Parameters

Parameter	Description
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.

HEX2DEC("CAB007")

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

HOUR(time)

Parameters

Parameter	Description
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.

HOUR("6/24/2012 7:30")

HOUR([START])

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

IF(test, then value, otherwisevalue)

Parameters

Parameter	Description
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.

IF(AND([STATE]="Massachusetts", [HEALTHCARE_COVERAGE] > 30), MOCK_FIRST_NAME_FEMALE, MOCK_FIRST_NAME_MALE)

IFERROR

The IFERROR function is used to trap and handle errors in a formula. If a formula results in an error, IFERROR returns a specified value; otherwise, it returns the result of the formula.

Syntax

IFERROR(value, alternate_value)

Parameters

Parameter	Description
value	The expression, formula or column that is checked for an error.
alternate_value	The value or mock value to return if the formula evaluates to an error.

Return value

The value if it does not contains an error value, or the alternative value if it does.

Example

The following formula returns random digit if, for example, healthcare expense for a patient is zero. Since a number can not be divided by 0, it will show error message.

IFERROR([HEALTHCARE_COVERAGE]/[HEALTHCARE_EXPENSES], MOCK_RANDOM_DIGIT)

IFNA

The IFNA function returns a specified value if a formula results in a N/A error; otherwise, it returns the result of the formula.

Syntax

IFNA(value, alternate_value)

Parameters

Parameter	Description
value	The expression, formula or column that is checked for the N/A error.
alternate_value	The value or mock value to return if the formula evaluates to a N/A error.

Return value

The value if it does not contains the N/A (value not available) error value, or the alternative value if it does.

Example

The following formula return mock addresses if there is no address recorded for a row or person in that table.

IFNA([ADDRESS], MOCK_ADDRESS)

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

IFS(condition1, number1, condition2, number2..., condition_n, number_N)

Parameters

Parameter	Description
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".

IFS([GENDER] = "F", MOCK_NAME_FEMALE, [GENDER] = "M", MOCK_NAME_MALE)

INDEX

The INDEX function returns the value of an element in a table or array based on the specified row and column number indexes. Use the array form of INDEX when the first argument is an array constant.

• If both row and column are provided, INDEX returns the value in the cell at the intersection of the specified row and column.

• The row and column must refer to a valid cell within the array; otherwise, INDEX returns an error.

• If you set row or column to 0 (zero), INDEX returns the entire column or row as an range, respectively. To utilize these values as an array, enter the INDEX function as an array formula.

Syntax

INDEX(number, row, column)

Parameters

Parameter	Description
number	Column or an array constant. If the range contains only one row or column, the corresponding row or column argument is optional. If the array has more than one row and column, and only row or column is specified, INDEX returns the entire row or column.
row	Specifies the row in the range from which to return a value. If row is omitted, column must be specified.
column	Specifies the column in the range from which to return a value. If column is omitted, row must be specified.

Return value

The contents of specified value by row and column number. The column number is optional and defaults to 1.

Example

The following formulas return different results.

INDEX({[HEALTHCARE_COVERAGE], [HEALTHCARE_EXPENSES]; [LAT], [LON]},0,0)

Formula above returns [ [HEALTHCARE_COVERAGE, HEALTHCARE_EXPENSES], [LAT, LON] ].

INDEX({[HEALTHCARE_COVERAGE], [HEALTHCARE_EXPENSES]; [LAT], [LON]},0,1)

Formula above returns [HEALTHCARE_COVERAGE, LAT].

INDEX({[HEALTHCARE_COVERAGE], [HEALTHCARE_EXPENSES]; [LAT], [LON]},0,2)

Formula above returns [HEALTHCARE_EXPENSES, LON].

INDEX({[HEALTHCARE_COVERAGE], [HEALTHCARE_EXPENSES]; [LAT], [LON]},1,2)

Formula above returns HEALTHCARE_EXPENSES.

INDEX({[HEALTHCARE_COVERAGE], [HEALTHCARE_EXPENSES]; [LAT], [LON]},2,2)

Formula above returns LON.

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

ISBLANK(value)

Parameters

Parameter	Description
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.

ISBLANK([ZIP])

ISERR

The ISERR function checks whether a value is an error. It returns TRUE if the value is any other error type, such as DIV/0!, VALUE!, REF!, NUM!, NAME?, or NULL!. If the value is not an error or is specifically the N/A! error, ISERR returns FALSE.

This function is useful for identifying and handling specific types of errors within a table.

Syntax

ISERR(value)

Parameters

Parameter	Description
value	The column or expression to be checked for an error, excluding N/A!.

Return value

TRUE if the value is an error other than N/A!; FALSE if the value is not an error or is N/A!.

Example

The following formula checks if the value in a specified column and row is an error, excluding N/A!.

ISERR([ZIP])

ISERROR

The ISERROR function checks whether a value is any type of error. It returns TRUE if the value is an error, such as DIV/0!, VALUE!, REF!, NUM!, NAME?, NULL!, or N/A!. If the value is not an error, ISERROR returns FALSE.

This function is useful for identifying errors within a table, allowing you to manage or correct them as needed.

Syntax

ISERROR(value)

Parameters

Parameter	Description
value	The column or expression to be checked for any type of error.

Return value

TRUE if the value is an error; FALSE if the value is not an error.

Example

The following formula checks if the value in a specific row and column results in an error.

ISERROR([ZIP])

ISEVEN

The ISEVEN function checks whether a value is an even integer. It returns TRUE if the value is even, and FALSE if the value is odd. This function is useful for quickly determining the parity of a value within a table.

Syntax

ISEVEN(value)

Parameters

Parameter	Description
value	The column or expression to be checked for evenness.

Return value

TRUE if the value is an even integer; FALSE if the value is odd.

Example

The following formula checks if the value in Healthcare Expenses column is even.

ISEVEN([HEALTHCARE_EXPENSES])

ISLOGICAL

The ISLOGICAL function checks whether a value is a logical value, specifically TRUE or FALSE. It returns TRUE if the value is logical, and FALSE otherwise. This function is useful for verifying whether a value in a table is a logical expression.

Syntax

ISLOGICAL(value)

Parameters

Parameter	Description
value	The column or expression to be checked for a logical value.

Return value

TRUE if the value is TRUE or FALSE; FALSE if the value is not logical.

Example

The following formula checks if the value in Healthcare Expenses column is a logical value.

ISLOGICAL([HEALTHCARE_EXPENSES])

ISNA

The ISNA function checks whether a value is the N/A! error. It returns TRUE if the value is N/A!, and FALSE otherwise. This function is useful for identifying missing or unavailable data within a table.

Syntax

ISNA(value)

Parameters

Parameter	Description
value	The column or expression to be checked for the N/A! error.

Return value

TRUE if the value is the N/A! error; FALSE if the value is not.

Example

The following formula checks if the value in Healthcare Expenses column results in a N/A! error.

ISNA([HEALTHCARE_EXPENSES])

ISNONTEXT

The ISNONTEXT function checks whether a value is not text. It returns TRUE if the value is not text or if the value is blank, and FALSE if the value is text. This function is useful for distinguishing non-text entries within a table. An empty string ("") is considered text.

Syntax

ISNONTEXT(value)

Parameters

Parameter	Description
value	The value or column you want to check.

Return value

TRUE if the value is not text or is blank; FALSE if the value is text.

Example

The following formula checks if the value in Healthcare Expenses column is not text or is blank.

ISNONTEXT([HEALTHCARE_EXPENSES])

ISNUMBER

The ISNUMBER function checks whether a value is numeric. It returns TRUE if the value is a number, and FALSE otherwise. This function is useful for identifying numeric entries within a table.

Syntax

ISNUMBER(value)

Parameters

Parameter	Description
value	The value or column you want to test.

Return value

TRUE if the value is numeric; otherwise, FALSE.

Example

The following formula checks if the value in Healthcare Expenses column is numeric or not..

ISNUMBER([HEALTHCARE_EXPENSES])

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

ISODD(value)

Parameters

Parameter	Description
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.

ISODD([HEALTHCARE_EXPENSES])

ISTEXT

The ISTEXT function checks whether a value is text. It returns TRUE if the value is text, and FALSE if it is not. This function is useful for identifying text entries within a table.

Syntax

ISTEXT(value)

Parameters

Parameter	Description
value	The value or column you want to check.

Return value

TRUE if the value is text; otherwise, FALSE.

Example

The following formula checks if the value in Healthcare Expenses column is text.

ISTEXT([HEALTHCARE_EXPENSES])

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

LEFT("text", number)

Parameters

Parameter	Description
"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.

LEFT([STATE], MOCK_RANDOM_DIGIT)

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

LEN("text")

Parameters

Parameter	Description
"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.

LEN([BIRTHPLACE])

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

LN(number)

Parameters

Parameter	Description
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.

LN([HEALTHCARE_EXPENSES])

LOG

The LOG function returns the logarithm of a specified number to a base that you define.

Syntax

LOG(number, base)

Parameters

Parameter	Description
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.

LOG([HEALTHCARE_EXPENSES], 2)

LOG10

The LOG10 function returns the base-10 logarithm of a specified number.

Syntax

LOG10(number)

Parameters

Parameter	Description
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.

LOG10([HEALTHCARE_EXPENSES])

LOWER

The LOWER function converts all uppercase letters in a text string to lowercase.

Syntax

LOWER("text")

Parameters

Parameter	Description
"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.

LOWER([ADDRESS])

MATCH

The MATCH function searches for a specified value in a range and returns the relative position of that value within the range.

Syntax

MATCH(Searchcriterion, Lookuparray, MatchType)

Parameters

Parameter	Description
Searchcriterion	The value you want to find in the columns. This can be a number, text, logical value. For example, if you're looking up a person's name in a list to find their phone number, the person's name is the columns.
Lookuparray	The columns being searched for the Searchcriterion.
MatchType	A number that specifies how it should match the Searchcriterion with values in the columns. The default is 1. 1: Finds the largest value less than or equal to Searchcriterion (requires the data to be sorted in ascending order). 0: Finds the first value exactly equal to Searchcriterion. -1: Finds the smallest value greater than or equal to Searchcriterion (requires the data to be sorted in descending order).

Return value

The relative position of an item in an array that matches a specified value.

Example

The following formula checks the value "Massachusetts" in given array and returns the number 3 since that word appears in the "STATE" column.

MATCH("Massachusetts",{[BIRTHPLACE],[ADDRESS],[STATE],[COUNTY],[CITY]},0)

MAX

The MAX function returns the largest value from a set of specified values.

• The arguments can be numbers, names or arrays.

• Logical values and text representations of numbers that are directly entered into the list of arguments are included in the calculation.

• If an argument is an array, only the numeric values within that array are considered. Empty values, logical values, or text within the array are ignored.

• If no numbers are provided in the arguments, MAX returns 0.

• Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error.

• If you need to include logical values and text representations of numbers in a reference as part of the calculation, use the MAXA function.

Syntax

MAX(number1, number2, ...number30)

Parameters

Parameter	Description
number1, number2, ...number30	number1 is required, while subsequent number columns are optional. You can provide 1 to 30 number columns for which you want to find the maximum value.

Return value

The maximum value in a list of arguments.

Example

The following formula returns the maximum value in the given list which is 55.

MAX(15, 3, "DOG", TRUE, 55, 30)

MAXA

The MAXA function returns the largest value from a list of arguments, considering numbers, logical values, and text representations of numbers.

• Arguments can include numbers, names, arrays, text representations of numbers, or logical values (TRUE or FALSE) within a reference.

• Logical values and text representations of numbers directly entered into the arguments are included in the calculation.

• If an argument is an array, only the values within that array are used. Empty cells and text 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.

• Arguments containing TRUE are evaluated as 1, while arguments containing text or FALSE are evaluated as 0.

• If the arguments contain no values, MAXA returns 0.

• If you do not want to include logical values and text representations of numbers in the calculation, use the MAX function instead.

Syntax

MAXA(value1, value2, ... value30)

Parameters

Parameter	Description
value1	The first column, number or mocker for which you want to find the largest value.
value2, ... value30	Additional columns, numbers or mockers (up to 30) for which you want to find the largest value.

Return value

The maximum value in a list of arguments.

Example

The following formula returns the maximum value in the given list which is 55.

MAXA(15, 3, "DOG", TRUE, 55, 30)

MEDIAN

The MEDIAN function returns the median of a given set of numbers. The median is the middle value in a sorted list of numbers.

• If there is an even number of values in the set, MEDIAN calculates the average of the two middle numbers.

• Arguments can be numbers or names, arrays that contain numbers.

• Logical values and text representations of numbers directly entered into the arguments are included in the calculation.

• If an array argument contains text, logical values, or empty values, those values are ignored; however, cells with a value of zero are included.

• Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error.

Syntax

MEDIAN(number1, number2, ...number30)

Parameters

Parameter	Description
number1, number2, ...number30	number1 is required, and subsequent numbers, columns or mockers are optional. You can provide 1 to 30 number columns for which you want to find the median.

Return value

The median of a set of numbers.

Example

The following formula returns the median of a set of given numbers, which is 15.

MEDIAN(15, 3, "DOG", TRUE, 55, 30)

MIN

The MIN function returns the smallest number in a set of values.

• Arguments can be numbers, names, arrays, or values that contain numbers.

• Logical values and text representations of numbers directly entered into the arguments are included in the calculation.

• If an argument is an array, only the numeric values within that array are considered. Empty values, logical values, or text within the array are ignored.

• If no numbers are provided, MIN returns 0.

• 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 MINA function.

Syntax

MIN(number1, number2, ...number30)

Parameters

Parameter	Description
number1, number2, ...number30	number1 is required, and subsequent numbers, columns or mockers are optional. You can provide 1 to 30 number columns for which you want to find the minimum value.

Return value

The minimum value in a list of arguments.

Example

The following formula returns the minimum value in a list of given arguments, which is TRUE (1).

MIN(15, 3, "DOG", TRUE, 55, 30)

MINA

The MINA function returns the smallest value from a list of arguments, considering numbers, logical values, and text representations of numbers.

Syntax

MINA(value1, value2, ... value30)

Parameters

Parameter	Description
value1, value2, ... value30	value1 is required, and subsequent columns, numbers or mockers are optional. You can provide 1 to 30 columns for which you want to find the smallest value.

Return value

The minimum value in a list of arguments.

Example

The following formula returns the minimum value in a list of given arguments, which is -14.

MIN(15, 3, "DOG", TRUE, 55, 30, FALSE, -14)

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

MINUTE(time)

Parameters

Parameter	Description
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.

MINUTE("4:30:45 PM")

MINUTE([START])

MOD

The ISTEXT function checks whether a value is text. It returns TRUE if the value is text, and FALSE if it is not. This function is useful for identifying text entries within a table.

• If the divisor is 0, MOD returns the #DIV/0! error value.

• The MOD function can be expressed using the INT function as follows: MOD(n, d) = n - d*INT(n/d)

Syntax

MOD(dividend, divisor)

Parameters

Parameter	Description
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.

MOD(5, 2)	Remainder of 5/2	1
MOD(-5, 2)	Remainder of -5/2. The sign is the same as divisor	1
MOD(5, -2)	Remainder of 5/-2. The sign is the same as divisor	-1
MOD(-5, -2)	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

MONTH(number)

Parameters

Parameter	Description
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.

MONTH([BIRTHDATE])

NA

The NA function returns the error value N/A, which stands for "no value is available." This error is useful for marking missing information. By entering N/A where data is missing, you can prevent empty values from being unintentionally included in calculations. When a formula references a value marked as N/A, it returns the N/A error.

• You must include the empty parentheses () with the function name; otherwise, it will not be recognized as a function.

Syntax

NA(value)

Parameters

Parameter	Description
value	The value, column or mocker you want to check.

Return value

N/A error value.

Example

The following formula checks N/A error value.

NA([errors])

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

NOT(Logicalvalue)

Parameters

Parameter	Description
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.

IF(AND(NOT([GENDER]="M"),NOT([HEALTHCARE_EXPENSES]<100)),MOCK_NAME_FEMALE, MOCK_NAME_MALE)

ODD

The ODD function rounds a number up to the nearest odd integer.

• If the number is nonnumeric, the function returns an error.

• The function always rounds a value up and away from zero, regardless of its sign. If the number is already an odd integer, no rounding occurs.

Syntax

ODD(number)

Parameters

Parameter	Description
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.

ODD(3.4)	Rounds 3.4 up to the nearest odd integer.	5
ODD(3)	Rounds 3 up to the nearest odd integer.	3
ODD(6)	Rounds 6 up to the nearest odd integer.	7
ODD(-3)	Rounds -3 up to the nearest odd integer.	-3
ODD(-4)	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 TRUE or FALSE, 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, OR returns an error.

• You can use an OR array formula to check if a value occurs within an array.

Syntax

OR(Logicalvalue1, Logicalvalue2 ...Logicalvalue30)

Parameters

Parameter	Description
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.

OR([HEALTHCARE_COVERAGE]<100, [HEALTHCARE_EXPENSES]>250)

PI

The PI function returns the mathematical constant pi (π), accurate to 15 digits, which is approximately 3.14159265358979.

Syntax

PI()

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.

PI()*(5^2)

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

POWER(base, exponent)

Parameters

Parameter	Description
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.

POWER(1.1, 3)

POWER([AMOUNT], [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

PRODUCT(number1, number2, ...number30)

Parameters

Parameter	Description
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.

PRODUCT(2,3,4,5)

PRODUCT([BASE_COST], [DISPENSES], [PAYER_COVERAGE])

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

PROPER("text")

Parameters

Parameter	Description
"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.

PROPER("5-star 99hoTels AMSTERDAM")

PROPER([ADDRESS])

PROPER(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 rate and nper arguments must be based on the same units of time. For example, if rate is calculated using months, nper should 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

PV(Rate, Nper, Pmt, Fv, Type)

Parameters

Parameter	Description
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.

PV(0.06/12,4*12,-250.99)

PV([RATE], [NPER], [PMT], [FV], [TYPE])

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

QUARTER(date)

Parameters

Parameter	Description
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.

QUARTER(DATE(2012, 8, 24))

QUARTER([BIRTHDATE])

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

QUOTIENT(dividend, divisor)

Parameters

Parameter	Description
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.

QUOTIENT(-12.5, 5.3)

QUOTIENT([REVENUE], [NUMBER_OF_SALES])

RADIANS

The RADIANS function converts an angle from degrees to radians.

Syntax

RADIANS(number)

Parameters

Parameter	Description
number	The angle in degrees, column or mocker that you want to convert to radians.

Return value

Degrees that is converted to radians.

Example

The following formula returns 180 radians that is converted from degrees which is PI (π).

RADIANS(180)

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

RAND()

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:

a + (b - a) * RAND()

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.

5 + (3 - 5) * RAND()

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

RANDBETWEEN(Lowerbound, Upperbound)

Parameters

Parameter	Description
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.

RANDBETWEEN(3, 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

REPLACE(old_text, start_num, num_chars, new_text)

Parameters

Parameter	Description
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.

REPLACE("Hello Jupyter!",6,7,"Moon")

REPLACE([PASSPORT],6,7,"004A")

RIGHT

The RIGHT function returns a specified number of characters from the right side of a string.

Syntax

RIGHT("text", number)

Parameters

Parameter	Description
"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).

RIGHT("Hello Earth!", 6)

RIGHT([STATE], MOCK_RANDOM_DIGIT)

ROMAN

The ROMAN function converts an Arabic numeral to a Roman numeral, returning the result as text.

Syntax

ROMAN(number, mode)

Parameters

Parameter	Description
number	The Arabic numeral or column you want to convert to a Roman numeral.
mode	A number, column or mocker that specifies the style of the Roman numeral. The style ranges from Classic to Simplified, with the numeral becoming more concise as the form value increases.

Return value

Numbers that are converted to roman form.

Example

The following formulas return relevant roman forms.

ROMAN(999,0)	Classic roman numeral style for 999 (CMXCIX)	CMXCIX
ROMAN(999,0)	More concise version for 999 (LMVLIV)	LMVLIV
ROMAN(999,0)	More concise version for 999 (XMIX)	XMIX
ROMAN(999,0)	More concise version for 999 (VMIV)	VMIV
ROMAN(999,0)	Simplified version for 999 (IM)	IM

ROUND

The ROUND function rounds a number to a specified number of digits.

• If Count is greater than 0, the Number is rounded to the specified number of decimal places.

• If Count is 0, the Number is rounded to the nearest integer.

• If Count is less than 0, the Number is rounded to the left of the decimal point.

• To always round up (away from zero), use the ROUNDUP function.

• To always round down (toward zero), use the ROUNDDOWN function.

Syntax

ROUND(number, count)

Parameters

Parameter	Description
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.

ROUND(3.25, 1)	Rounds 3.25 to one decimal place	3.2
ROUND(5.149, 1)	Rounds 5.149 to one decimal place	5.1
ROUND(-6.745, 2)	Rounds -6.745 to two decimal places	-6.75
ROUND(23.9, -1)	Rounds 23.9 to one decimal place to the left of the decimal point	20
ROUND(575.9, -3)	Rounds 575.9 to the nearest multiple of 1000	1000
ROUND(2.33, -1)	Rounds 2.33 to the nearest multiple of 10	0
ROUND(-51.52, -2)	Rounds -51.52 to the nearest multiple of 100	-100

ROUNDDOWN

The ROUNDDOWN function rounds a number down, toward zero.

• ROUNDDOWN behaves similarly to ROUND, except that it always rounds a number down.

• If Count is greater than 0, the number is rounded down to the specified number of decimal places.

• If Count is 0, the number is rounded down to the nearest integer.

• If Count is less than 0, the number is rounded down to the left of the decimal point.

Syntax

ROUNDDOWN(number, count)

Parameters

Parameter	Description
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.

ROUNDDOWN(5.3, 0)	Rounds 5.3 down to zero decimal place	5
ROUNDDOWN(66.8,0)	Rounds 66.8 down to zero decimal place	66.8
ROUNDDOWN(1.24279, 3)	Rounds 1.24279 down to three decimal places	1.242
ROUNDDOWN(-1.24279, 1)	Rounds -1.24279 down to one decimal places	-1.2
ROUNDDOWN(12427.98637, -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).

• ROUNDUP behaves similarly to ROUND, except that it always rounds a number up.

• If Count is greater than 0, the Number is rounded up to the specified number of decimal places.

• If Count is 0, the Number is rounded up to the nearest integer.

• If Count is less than 0, the Number is rounded up to the left of the decimal point.

Syntax

ROUNDUP(number, count)

Parameters

Parameter	Description
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.

ROUNDUP(5.3, 0)	Rounds 5.3 up to zero decimal place	6
ROUNDUP(66.8,0)	Rounds 66.8 up to zero decimal place	67
ROUNDUP(1.24279, 3)	Rounds 1.24279 up to three decimal places	1.243
ROUNDUP(-1.24279, 1)	Rounds -1.24279 up to one decimal places	-1.3
ROUNDUP(12427.98637, -2)	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

SECOND(time)

Parameters

Parameter	Description
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.

SECOND("2:25:55 PM")

SECOND([START_TIME])

SIN

The SIN function returns the sine of a given angle. If your angle is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians before using the SIN function.

Syntax

SIN(number)

Parameters

Parameter	Description
number	The angle in radians, column or mocker for which you want to find the sine.

Return value

The sine of the given angle (in radians).

Example

The following formula calculates sine of 30 degrees which is 0.5.

SIN(RADIANS(30))

SINH

The SINH function returns the hyperbolic sine of a given number.

Syntax

SINH(number)

Parameters

Parameter	Description
number	Any real number, column or mocker for which you want to calculate the hyperbolic sine.

Return value

The hyperbolic sine of the given value.

Example

The following formula calculates the hyperbolic sine of 0.02546 * 1.468 which is 0.37.

SINH(0.02546 * 1.468)

SQRT

The SQRT function returns the positive square root of a given number.

Syntax

SQRT(number)

Parameters

Parameter	Description
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".

SQRT(49)

SQRT([PAYER_COVERAGE])

STDEV

The STDEV function estimates the standard deviation based on a sample. The standard deviation measures how much values in a dataset deviate from the average (mean).

• STDEV assumes that its arguments represent a sample of the population. If your data represents the entire population, use the STDEV.P function instead.

• The standard deviation is calculated using the "n-1" method, which adjusts for the fact that the data is a sample rather than the entire population.

• 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 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 STDEVA function.

Syntax

STDEV(value1, value2, ... value30)

Parameters

Parameter	Description
value1	The first number, column or mocker in your sample of the population.
value2, ... value30	Additional arguments in your sample, up to 30 total arguments.

Return value

Standard deviation of a sample.

Example

The following formula calculates standard deviation of the sample below which is 26.7

STDEV(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)

STDEV.P

The STDEVP function calculates the standard deviation based on the entire population provided as arguments. The standard deviation measures how much the values in a dataset deviate from the average (mean).

Syntax

STDEV.P(value1, value2, ... value30)

Parameters

Parameter	Description
value1	The first number, column or mocker in your population.
value2, ... value30	Additional arguments in your population, up to 30 total arguments.

Return value

Standard deviation of a population.

Example

The following formula calculates standard deviation of the population below which is 25.4.

STDEV.P(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)

STDEV.S

The STDEV.S function estimates the standard deviation based on a sample, ignoring logical values and text in the sample. The standard deviation measures how much the values in a dataset deviate from the average (mean).

• STDEV.S assumes that its arguments represent a sample of the population. If your data represents the entire population, use the STDEV.P function instead.

• The standard deviation is calculated using the "n-1" method.

• Arguments can be numbers, names, arrays, or values that contain numbers.

• 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 STDEVA function.

Syntax

STDEV.S(value1, value2, ... value30)

Parameters

Parameter	Description
value1	The first number, column or mocker in your sample of the population.
value2, ... value30	Additional arguments in your sample, up to 254 total arguments.

Return value

Standard deviation of a sample.

Example

The following formula calculates standard deviation of the sample below which is 26.7.

STDEV.S(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)

STDEVA

The STDEVA function estimates the standard deviation based on a sample, considering numbers, text representations of numbers, and logical values. The standard deviation measures how much values in a dataset deviate from the average (mean).

• STDEVA assumes that its arguments represent a sample of the population. If your data represents the entire population, use the STDEVPA function instead.

• The standard deviation is calculated using the "n-1" method.

• Arguments can include numbers, names, arrays, text representations of numbers, or logical values such as TRUE and FALSE.

• Arguments containing TRUE are evaluated as 1; arguments containing text or FALSE are evaluated 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 STDEV function instead.

Syntax

STDEVA(value1, value2, ... value30)

Parameters

Parameter	Description
value1, value2, ... value30	value1 is required, and subsequent values are optional. You can provide 1 to 30 values corresponding to a sample of a population.

Return value

Standard deviation of the sample.

Example

The following formula calculates standard deviation of the sample below which is 26.7.

STDEVA(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)

STDEVP

The STDEVP function calculates the standard deviation based on the entire population provided as arguments. The standard deviation measures how much values in a dataset deviate from the average (mean).

• STDEVP assumes that its arguments represent the entire population. If your data represents only a sample of the population, use the STDEV function instead.

• For large sample sizes, STDEV and STDEVP return approximately equal values.

• The standard deviation is calculated using the "n" method.

• 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 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 STDEVPA function.

Syntax

STDEVP(value1, value2, ... value30)

Parameters

Parameter	Description
value1	The first number, column or mocker in your population.
value2, ... value30	Additional numbers, columns or mockers in your population, up to 30 total arguments.

Return value

Standard deviation of a population.

Example

The following formula calculates standard deviation of the population below which is 25.4.

STDEVP(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)

STDEVPA

The STDEVPA function calculates the standard deviation based on the entire population provided as arguments, including text and logical values. The standard deviation measures how much values in a dataset deviate from the average (mean).

• STDEVPA assumes that its arguments represent the entire population. If your data represents a sample of the population, use the STDEVA function instead.

• For large sample sizes, STDEVA and STDEVPA return approximately equal values.

• The standard deviation is calculated using the "n" method.

• Arguments can include numbers, names, arrays, text representations of numbers, or logical values such as TRUE and FALSE.

• Text representations of numbers entered directly into the argument list are included in the calculation.

• Arguments containing TRUE are evaluated as 1, while arguments containing text or FALSE are evaluated 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 STDEVP function.

Syntax

STDEVPA(value1, value2, ... value30)

Parameters

Parameter	Description
value1, value2, ... value30	value1 is required, and subsequent values are optional. You can provide 1 to 30 values corresponding to a population.

Return value

Standard deviation of a population.

Example

The following formula calculates standard deviation of the population below which is 25.4.

STDEVPA(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)

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

SUBSTITUTE(text, old_text, new_text, occurrence)

Parameters

Parameter	Description
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".

SUBSTITUTE("April 2, 2012", "2", "3", 3)

SUBSTITUTE([PASSPORT], "X", "S", 1)

SUM

The SUM function adds values together.

Syntax

SUM(number1, number2, ...number30)

Parameters

Parameter	Description
number1, number2, ...number30	number1 is the first value you want to add. number2-30 are the additional columns you want to add, up to a total of 30.

Return value

The values of the specified arguments that are summed up.

Example

The following formula sums up the values of the arguments below which are healthcare coverage, another number and an array .

SUM([HEALTHCARE_COVERAGE], 1414.45, {45, 66, 99})

SUMIF

The SUMIF function is used to sum values in a range that meet specific criteria.

Syntax

SUMIF(range, criteria, sumrange)

Parameters

Parameter	Description
range	The range of numbers, columns or mockers that you want to evaluate based on the criteria. The values in this range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may also contain dates in standard format.
criteria	The condition that defines which values in the column will be added. This can be a number, expression, text, or function. You can use wildcard characters: a question mark (?) to match any single character, and an asterisk (*) to match any sequence of characters. To find an actual question mark or asterisk, precede the character with a tilde (~). Text criteria or criteria containing logical or mathematical symbols must be enclosed in double quotation marks ("). Numeric criteria do not require quotation marks.
sumrange	The cells to be summed if you want to sum values other than those in the column. If omitted, the column values that meet the criteria will be summed. Sumrange should be the same size and shape as column. If it isn't, performance may suffer, and the function will sum cells starting with the first cell in Sumrange but with the same dimensions as column.

Return value

The values that belong to the specified range and meet the specified condition that are summed up.

Example

The following formula calculates the sum of the healthcare expenses that are over $1414.45.

SUMIF([HEALTHCARE_EXPENSES], ">1414.45")

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

SWITCH(expression1, value1, expression2, value2..., expression_n, value_n)

Parameters

Parameter	Description
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".

SWITCH(3,1,"Dog",2,"Cat",3,"Horse","?")

TAN

The TAN function returns the tangent of a given angle. If the angle is in degrees, multiply it by PI()/180 or use the RADIANS function to convert it to radians before using the TAN function.

Syntax

TAN(number)

Parameters

Parameter	Description
number	The angle in column, number or mocker for which you want to calculate the tangent.

Return value

The tangent of the given angle (in radians).

Example

The following formula calculates the tangent of the 45 degrees (in radians) which is 1.

TAN(RADIANS(45))

TANH

The TANH function returns the hyperbolic tangent of a given number.

Syntax

TANH(number)

Parameters

Parameter	Description
[column]	Any real number, column or mocker for which you want to calculate the hyperbolic tangent.

Return value

The hyperbolic tangent of the given value.

Example

The following formula calculates the hyperbolic tangent of -1.5 which is -0.9.

TANH(-1.5)

TODAY

The TODAY function returns the current date.

Syntax

TODAY()

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.

YEAR(TODAY()) - 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

TRIM("text")

Parameters

Parameter	Description
"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.

TRIM(" Senatus Populusque Romanus ")

TRIM([ADDRESS])

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()) returns TRUE if 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 returns TRUE if the condition is met.

If the condition is not met, both examples return FALSE.

Syntax

TRUE()

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.

IF(2=2,TRUE, FALSE)

UPPER

The UPPER function converts text to uppercase.

Syntax

UPPER("text")

Parameters

Parameter	Description
"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.

UPPER("hello world!")

UPPER([ADDRESS])

VAR

The VAR function calculates the variance based on a sample of a population.

• VAR assumes that its arguments represent a sample of the population. If your data represents the entire population, use the VARP function 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 VARA function.

Syntax

VAR(value1, value2, ... value30)

Parameters

Parameter	Description
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(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)

VAR([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COVERED_ENCOUNTERS], [COVERED_PROCEDURES])

VAR.P

The VAR.P function calculates the variance based on the entire population, ignoring logical values and text.

• VAR.P assumes that its arguments represent the entire population. If your data represents only a sample of the population, use the VAR.S function 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 VARPA function.

Syntax

VAR.P(value1, value2, ... value30)

Parameters

Parameter	Description
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.P(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)

VAR.P([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COVERED_ENCOUNTERS], [COVERED_PROCEDURES])

VAR.S

The VAR.S function estimates variance based on a sample, ignoring logical values and text.

• VAR.S assumes that its arguments represent a sample of the population. If your data represents the entire population, use the VAR.P function 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 VARA function.

Syntax

VAR.S(value1, value2, ... value30)

Parameters

Parameter	Description
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.

VAR.S(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)

VAR.S([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COVERED_ENCOUNTERS], [COVERED_PROCEDURES])

VARA

The VARA function estimates variance based on a sample, considering numbers, text representations of numbers, and logical values.

• VARA assumes that its arguments represent a sample of the population. If your data represents the entire population, use the VARPA function instead.

• Arguments can include numbers, names, arrays, text representations of numbers, or logical values such as TRUE and FALSE.

• Logical values and text representations of numbers entered directly into the argument list are included in the calculation.

  • Arguments containing TRUE evaluate as 1.

  • Arguments containing text or FALSE evaluate 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 VAR function.

Syntax

VARA(value1, value2, ... value30)

Parameters

Parameter	Description
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.

VARA(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)

VARA([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COVERED_ENCOUNTERS], [COVERED_PROCEDURES])

VARP

The VARP function calculates the variance based on the entire population.

• VARP assumes that its arguments represent the entire population. If your data represents a sample of the population, use the VAR function 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 VARPA function.

Syntax

VARP(value1, value2, ... value30)

Parameters

Parameter	Description
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.

VARP(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)

VARP([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COVERED_ENCOUNTERS], [COVERED_PROCEDURES])

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

WEEKNUM(date, [return_type])

Parameters

Parameter	Description
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.

WEEKNUM("24/8/2012")

WEEKNUM([BIRTHDATE])

XOR

The XOR function returns a logical Exclusive Or (XOR) of all the provided arguments.

• The arguments must evaluate to logical values such as TRUE or FALSE, 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, XOR returns the #VALUE! error.

• You can use an XOR array formula to check if a value occurs in an array.

• The result of XOR is TRUE when the number of TRUE inputs is odd and FALSE when the number of TRUE inputs is even.

Syntax

XOR(Logicalvalue1, Logicalvalue2 ... Logicalvalue30)

Parameters

Parameter	Description
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.

XOR([GENDER]="M", [HEALTHCARE_COVERAGE]<250, [HEALTHCARE_EXPENSES]>1000)

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

YEAR(number)

Parameters

Parameter	Description
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.

YEAR("24/8/2012")

YEAR([BIRTHDATE])

Info: The below functions are planned to be introduced in the future releases of Syntho.

Planned Functions for future releases

Function	Description	Format
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)