# Calculated columns

**Calculated Column** formulas can be used to perform a wide range of operations on data, from simple arithmetic to complex logical and statistical computations. Whether you're rounding numbers, extracting portions of dates, calculating averages, or transforming texts, these formulas provide the versatility to generate the data you need.

Here are some typical use cases for **Calculated Column** functions:

**Data Cleaning and Transformation**: Effortlessly clean and reformat data, such as trimming whitespace, changing text casing, or converting date formats.**Statistical Analysis**: Perform statistical calculations like averages, variances, or standard deviations to derive insights from numerical data sets.**Logical Operations**: Apply logical tests to data to create flags, indicators, or to filter and categorize data based on specific criteria.**Mathematical Operations**: Execute a variety of mathematical operations, enabling complex calculations like financial modelling or engineering calculations.**Text and Date Manipulation**: Extract or transform portions of text and date fields, which is particularly useful in data preparation for reporting or further analysis.

## Syntax

**Calculated Columns** are composed of a library of functions and operators used to build formulas and expressions in Syntho. When creating **calculated columns** in Syntho, you must follow specific syntax rules and best practices. Here are the fundamental syntax rules for **Calculated Column** expressions when creating **calculated columns**:

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

.**References**:**Column References**: When referring to a column within the same table, you can just use the column name in square brackets:`[ColumnName]`

.**Table References**: If you need to reference a column in a different table, use the table name followed by the column name in square brackets:`TableName[ColumnName]`

.If your table names use spaces or special characters, enclose the name in single quotes.

**Schema References**: References to tables outside of the existing schema are not supported.

**Functions**:**Calculated Column**includes a wide range of functions that can be used in your expressions, such as 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)`

.**Mockers**: Mockers can be added using the autosuggestion dropdown, using the syntax`MOCK_MOCKER_NAME`

, where MOCKER_NAME should be replaced with the relevant mocker name (e.g. FIRST_NAME). Any applied mockers in calculated columns will use the default mocker parameter settings. For each mocker, there is a mocker variant with and without consistent mapping. To enable consistent mapping, use the variant:`MOCK_CONSISTENT_ MOCKER_NAME`

.**Operators**:**Calculated Column**supports various operators for arithmetic calculations (`+`

,`-`

,`*`

,`/`

), and comparison (`<`

,`>`

,`<=`

,`>=`

,`=`

,`<>`

) operations.**Constants**: You can use constants like numbers (`123`

), strings (`"text"`

), and dates (`DATE(2020, 12, 31)`

).**Handling Errors**: Use error handling functions like`IFERROR`

to manage errors in your expressions.**Whitespace and Line Breaks**: Whitespace and line breaks do not affect the interpretation of the formula but can make your formulas easier to read.

Here's an example of a **calculated column** formula that calculates the total sales plus tax for each row in a table:

In this formula, `[Total Sales]`

and `[Tax Rate]`

are existing columns in the same table, and the formula calculates the total sales including tax for each row in the table.

Another example is the first name mocker. Imagine having a table with a column for first names. However, the user wants to generate male mock data for male names and female mock data for female names based on checking their gender in the gender column. This request can be expressed using the formula below:

If you would like to add a mocker to a calculated column formula, type "`MOCK_`

" and you will see the names of mockers in the dropdown list. Either choose from the list or add the name of the mocker to the end of "`MOCK_`

", with whitespaces replaced by underscores. Therefore, mockers like "**First name female**" and "**Company Email**" should be entered as "`MOCK_FIRST_NAME_FEMALE`

" and "`MOCK_COMPANY_EMAIL`

," respectively. A complete list of mockers can be found in the Mockers section.

### Using Consistently Mapped Mockers

All mockers have variants to support consistent mapping. This ensures that input from the same column is consistently mocked.

**Example:**

The existing

`MOCK_FIRST_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.

## Supported calculated column functions

## ABS

Returns the absolute value of a number.

### Syntax

#### Parameters

### Return value

An absolute value of the number.

### Example

The following formulas calculate absolute values of the column "HEALTHCARE_COVERAGE", -5 and mock value.

## 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

#### Parameters

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

## 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

#### Parameters

### 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".

## 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

#### Parameters

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

## ACOTH

Returns the inverse hyperbolic cotangent of a number.

### Syntax

#### Parameters

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

## AND

The `AND`

function returns `TRUE`

if all provided arguments evaluate to `TRUE`

, and `FALSE`

if any argument evaluates to `FALSE`

.

A common use case for the `AND`

function is enhancing other functions that perform logical tests. For instance, the `IF`

function tests a condition and returns one value if the condition is `TRUE`

and another value if it is `FALSE`

. By using `AND`

as the `logical_test`

argument in the `IF`

function, you can test multiple conditions simultaneously rather than just one.

### Syntax

#### Parameters

### 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`

.

## ARABIC

Converts a Roman numeral into an Arabic numeral.

### Syntax

#### Parameters

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

## 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

#### Parameters

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

## 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

#### Parameters

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

## 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

#### Parameters

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

## 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

#### Parameters

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

## 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

#### Parameters

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

## 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

#### Parameters

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

## AVERAGE

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

### Syntax

#### Parameters

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

## 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

#### Parameters

### Return value

The average of the arguments.

### Example

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

## AVERAGEIF

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

### Syntax

#### Parameters

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

## BASE

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

### Syntax

#### Parameters

### Return value

Converts a positive integer to a specified base into a text from the numbering system.

### Example

The following formulas calculate the text representation of the number in the specified radix (base).

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

BASE(5, 2, 8) converts 5 into 101 and add 5 leading zeros to make the string 8 characters long, so answer should be 00000101.

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

Alternatively, use columns or mock values.

## CEILING

Rounds a number up, away from zero, to the nearest multiple of a specified significance. The function always rounds a value up, away from zero, regardless of the sign of the number. If the number is an exact multiple of the significance, no rounding is performed. If both the number and significance are negative, the value is rounded down, away from zero. If the number is negative and the significance is positive, the value is rounded up toward zero.

### Syntax

#### Parameters

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

## 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

#### Parameters

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

#### Parameters

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

## CHAR

Returns the character corresponding to a specified number.

### Syntax

#### Parameters

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

## 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

#### Parameters

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

## CLEAN

Removes all nonprintable characters from a given text. The `CLEAN`

function is useful for cleaning text imported from other applications that may contain characters not supported by your operating system. For example, it can remove low-level computer code that often appears at the beginning or end of data files and cannot be printed.

**Important**

The `CLEAN`

function is designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31). However, in the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157) that the `CLEAN`

function does not remove.

### Syntax

#### Parameters

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

## 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

#### Parameters

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

## COMPLEX

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

or `x + yj`

.

### Syntax

#### Parameters

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

## CONCATENATE

The `CONCATENATE`

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

### Syntax

#### Parameters

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

## 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

#### Parameters

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

## COSH

Calculates the hyperbolic cosine of a given number.

### Syntax

#### Parameters

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

## COT

Calculates the cotangent of a given angle in radians.

### Syntax

#### Parameters

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

## 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

#### Parameters

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

## 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

#### Parameters

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

## COUNTBLANK

The `COUNTBLANK`

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

### Syntax

#### Parameters

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

## 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

#### Parameters

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

## DATE

The `DATE`

function creates a date value by combining individual year, month, and day components. This function is useful for combining separate year, month, and day values into a single date.

### Syntax

#### Parameters

### Return value

The specified date as the number of full days since nullDate.

### Example

The following formula calculates the specified date as the number of full days since nullDate which is 2012-08-24 00:00:00.

## DATEADD

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

### Syntax

#### Parameters

### Return value

A column of date values.

### Example

The following formula calculates dates that are 20 years after the dates in the Birthdate column.

## DATEVALUE

#### Description

The `DATEVALUE`

function converts a date stored as text into a date. Note that the results of the `DATEVALUE`

function may vary depending on your computer's system date settings. The `DATEVALUE`

function is useful when you have dates in a text format that you want to filter, sort, format as dates, or use in date calculations.

### Syntax

#### Parameters

### Return value

A date that is converted from text to a date in datetime format.

### Example

The following formula calculates a date that is converted from text or a column to a date in datetime format.

Alternatively, following formulas can be used as well:

`DATEVALUE("6/24/2012")`

`DATEVALUE("2012/6/24")`

`DATEVALUE("24-JUNE-2024")`

`DATEVALUE("24-JUNE")`

## DAY

The `DAY`

function returns the day of a date represented by a serial number, with the day provided as an integer ranging from 1 to 31.

### Syntax

#### Parameters

### Return value

The day of the given date value.

### Example

The following formula calculates the day of the given date value which is 24.

## DAYS

The `DAYS`

function calculates the number of days between two dates.

### Syntax

#### Parameters

### Return value

The difference between two date values.

### Example

The following formula calculates the difference between two date values which is 10.

## DATEDIFF

The `DateDiff`

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

### Syntax

#### Parameters

### 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

## DEC2HEX

The `DEC2HEX`

function converts a decimal number to its hexadecimal equivalent.

### Syntax

#### Parameters

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

## DECIMAL

The `DECIMAL`

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

### Syntax

#### Parameters

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

## DEGREES

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

### Syntax

#### Parameters

### Return value

Degrees converted from radians.

### Example

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

## DELTA

The `DELTA`

function checks whether two values are equal. It returns `1`

if `number1`

equals `number2`

, and `0`

otherwise.

### Syntax

#### Parameters

### Return value

Returns TRUE (1) if both numbers are equal, otherwise returns FALSE (0).

### Example

The following formula checks if a patient's healthcare coverage and expenses are equal. If equal, it will return 1, otherwise 0.

## EVEN

The `EVEN`

function rounds a number up to the nearest even integer. The function always rounds a value up and away from zero, regardless of its sign. If the `number column`

is already an even integer, no rounding occurs.

### Syntax

#### Parameters

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

## 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

#### Parameters

### 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".

## FACT

Returns a factorial of a number.

### Syntax

#### Parameters

### 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".

## FACTDOUBLE

The `FACTDOUBLE`

function returns the double factorial of a specified number.

### Syntax

#### Parameters

### 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

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.**n**

## FALSE

Returns the logical value FALSE.

### Syntax

### Return value

The logical value FALSE.

### Example

The following formula returns the logical value FALSE.

## FLOOR

The `FLOOR`

function rounds a number down toward zero to the nearest multiple of a specified significance. When the `number`

is positive, it is rounded down and adjusted toward zero. When the `number`

is negative, it is rounded down and adjusted away from zero. If the `number`

is already an exact multiple of the `significance`

, no rounding occurs.

If either argument is nonnumeric, the function returns an error message.

If the

`number`

is positive and the`significance`

is negative, the function returns an error message.

### Syntax

#### Parameters

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

## 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

#### Parameters

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

## 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

#### Parameters

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

## 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

#### Parameters

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

## 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

#### Parameters

### Return value

Returns the harmonic average.

### Example

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

## HEX2DEC

The `HEX2DEC`

function converts a hexadecimal number to its decimal equivalent. If the input is not a valid hexadecimal number, the function returns an error.

### Syntax

#### Parameters

### Return value

The decimal number for the hexadecimal number entered.

### Example

The following formula calculates the decimal number for the hexadecimal number "CAB007", and result is 13283335.

## HOUR

The `HOUR`

function returns the hour component of a time value as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.). Time values are part of a date value and are represented as decimal numbers (e.g., 12:00 PM is represented as `0.5`

because it is half of a day).

### Syntax

#### Parameters

### Return value

Hour component of given time.

### Example

The following formula returns hour component of given time which is 7. Alternatively, use columns to find hours for every row.

## IF

The `IF`

function performs a logical test and returns one value if the test evaluates to TRUE and another value if it evaluates to FALSE. This function is commonly used to create conditional formulas.

### Syntax

#### Parameters

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

## 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

#### Parameters

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

## 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

#### Parameters

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

## IFS

The `IFS`

function checks multiple conditions and returns a value corresponding to the first condition that evaluates to TRUE. This function simplifies the use of multiple nested `IF`

statements, making it easier to read and manage multiple conditions.

To specify a default result, enter `TRUE`

as the final condition argument. If none of the other conditions are met, the corresponding value will be returned. For example, if all other conditions fail, a default value can be returned by using `TRUE`

as the final condition. If a condition argument is provided without a corresponding value, the function will return an error message. If a condition argument evaluates to something other than TRUE or FALSE, the function returns an error. If none of the conditions evaluate to TRUE, the function returns an error.

### Syntax

#### Parameters

### 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".

## 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

#### Parameters

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

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

.

Formula above returns `[HEALTHCARE_COVERAGE, LAT]`

.

Formula above returns `[HEALTHCARE_EXPENSES, LON]`

.

Formula above returns `HEALTHCARE_EXPENSES`

.

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

#### Parameters

### Return value

`TRUE`

if the value is blank; `FALSE`

if it contains any data.

### Example

The following formula checks if column ZIP is empty.

## 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

#### Parameters

### 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!`

.

## 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

#### Parameters

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

## 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

#### Parameters

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

## 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

#### Parameters

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

## 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

#### Parameters

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

## 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

#### Parameters

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