Execute formula

The content for the column may be a numeric or string constant, the NULL value, an empty string, a row value, or an operation consisting of constants, column references, functions, and operators. Non-string column values are converted to strings in a concatenation. Dates are converted to string values based on the current locale and format of the execution environment.

• Step name: Defines the name for a step. Provide a meaningful name so that anyone who edits steps in a pipeline will be able to identify the purpose of a step. For example, "Add Address column".
• Constructor: Accepts a formula in Excel format. The formula may include any or all of the following:
  • Constant: A value that is not calculated and, therefore, does not change. For example, the number 210 and the text "Quarterly Earnings" are constants.
  • Reference: A column reference. This consists of the column name enclosed in brackets, such as [Street] or [City].
  • Operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.
  • Function: An Excel function such as SUM and CONCAT that accepts and returns values. These consist of the function name and arguments enclosed in parentheses. Arguments may include numbers, text, column references, and functions. For example: CONCAT([Street], ", ", [City], ", ", [PostalCode])
• Output column: Specifies the name of the output column.
• Output Column Datatype: Choose from one of the possible column data types: String, Integer, Long, Float, Double, Boolean, Bigdecimal, Date, Datetime, and Time. Numeric values are converted to strings for a string type, and strings are converted to numeric values for the numeric types.
• Transformation Preview: The Transformation Preview table shows the output column and any columns referenced by the formula in the Constructor box. The table shows an error or is blank when there are errors in the formula or for unsupported conversions to an output data type (such as for the Integer data type when there are alpha characters "onetwothree" instead of numeric characters "123" in a string value).

Description

Returns the absolute value of a number.

Syntax

ABS(number)

The ABS function syntax has the following arguments.

• number Required. The real number of which you want the absolute value.

Example

Description

Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to π

Syntax

ACOS(number)

The ACOS function syntax has the following argument.

• number Required. The cosine of the angle you want. The value must be from -1 to 1.

Example

Description

Returns the inverse hyperbolic cosine of a number. The number must be greater than or equal to 1. The inverse hyperbolic cosine is the value whose hyperbolic cosine is number, so ACOSH(COSH(number)) equals number.

Syntax

ACOSH(number)

The ACOSH function syntax has the following argument:

• number Required. Any real number equal to or greater than 1.

Example

Description

Returns Boolean true if all of its arguments are TRUE.

Syntax

AND(logical_expression1,logical_expression2,...)

The AND function syntax has the following arguments:

logical_expression1,logical_expression2,... Series of logical expressions that evaluate to TRUE or FALSE.

Example

AND([Tens]<100,[Hundreds]>100)

AND([Tens]<100,[Hundreds]<100)

AND([Tens]<100,[Hundreds]>100,[Thousands]>1000)

Description

Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number. The returned angle is given in radians in the range -π/2 to π/2.

Syntax

ASIN(number)

The ASIN function syntax has the following argument:

• number Required. The sine of the angle. Must be from -1 to 1.

Example

Description

Returns the inverse hyperbolic sine of a number. The inverse hyperbolic sine is the value whose hyperbolic sine is number, so ASINH(SINH(number)) equals number.

Syntax

ASINH(number)

The ASINH function syntax has the following arguments:

• number Required. Any real number.

Example

Description

Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range -π/2 to π/2.

Syntax

ATAN(number)

The ATAN function syntax has the following arguments:

• number Required. The tangent of an angle.

Example

Description

Returns the arctangent, or inverse tangent, of the specified x- and y-coordinates. The arctangent is the angle from the x-axis to a line containing the origin (0, 0) and a point with coordinates (x_num, y_num). The angle is given in radians between -π and π, excluding -π.

Syntax

ATAN2(x_num, y_num)

The ATAN2 function syntax has the following arguments:

• x_num Required. The x-coordinate of the point.
• y_num Required. The y-coordinate of the point.

Comments

• A positive result represents a counterclockwise angle from the x-axis. A negative result represents a clockwise angle.
• ATAN2(ab) = ATAN(b/a), except that the ratio on the right side is undefined when a = 0.

Example

Description

Returns the inverse hyperbolic tangent of a number. The number must be between -1 and 1 (excluding -1 and 1). The inverse hyperbolic tangent is the value whose hyperbolic tangent is number, so ATANH(TANH(number)) equals number.

Syntax

ATANH(number)

The ATANH function syntax has the following argument:

• number Required. Any real number between 1 and -1.

Example

Description

Returns the average of the absolute deviations of data points from their mean. AVEDEV is a measure of the variability in a data set. Arguments are numeric columns in the data.

The equation for average deviation is:

(1/n)Σ|x-m|

Where n is the number of data points, x is a column value, and m is the mean of the values.

Syntax

AVEDEV(number1,number2,...)

The AVEDEV function syntax has the following arguments:

• number1,number2,... number1 is required, subsequent numbers are optional. 1 to 255 arguments for which you want the average of the absolute deviations. Nonnumeric columns and empty fields are ignored. Fields containing the 0 value are included..

Example

Description

Returns the average of the its arguments. Returns the average (arithmetic mean) of the arguments. For example, if the range of temperatures for a location, day 1 through day 4, contains temperatures, the formula AVERAGE([Day1],[Day2],[Day3],[Day4]) returns the average temperature for the four days.

Syntax

AVERAGE(number1,number2,...)

• number1 Required. The number, column reference, or column range to average.
• number2,... Optional. The number, column reference, or column range to average.
• Arguments that are error values or text that cannot be translated into numbers cause errors.

Notes

• If a field referenced by an argument contains text, logical values, or empty cells, those values are ignored. Cells with the value zero are included.
• A range specifies multiple fields in a single argument. The order does not have to match the order that columns appear in the sample data table. For example, this range specifies four columns (1, 3, 5, and 7):

  AVERAGE([column1]:[column5]:[column3]:[column7])

  .

Example

Description

Rounds a number to the nearest integer or to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula CEILING(4.42,0.05) to round prices up to the nearest nickel.

Syntax

CEILING(number, significance)

The CEILING function syntax has the following arguments:

• number Required. The value you want to round.
• significance Required. The multiple to which you want to round.

Notes

• If number is nonnumeric, an error occurs.
• Regardless of the sign of number, a value is rounded up when adjusted away from zero. If number is an exact multiple of significance, no rounding occurs
• If number is negative, and significance is negative, the value is rounded down, away from zero.
• If number is negative, and significance is positive, the value is rounded up towards zero.

Example

Description

Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files or data sources on other types of computers into characters.

Syntax

CHAR(number)

The CHAR syntax has the following argument:

• number Required. A number between 1 and 255 specifying a character. The character is from the character set used by Data Integrity Suite.

Example

Description

Removes all unprintable characters from text. Use CLEAN on text from other data sources that contains the first 32 nonprinting characters in 7-bit ASCII code (values 0 through 31).

Syntax

CLEAN(text)

The CLEAN function syntax has the following argument:

• Text Required. Any field information from which you want to remove nonprinting characters.

Example

Description

Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by Data Integrity Suite.

Syntax

CODE(text)

The CODE syntax has the following argument:

• text Required. The text for from which to code the first character.

Example

Description

Returns the number of combinations for a given number of objects. Use COMBIN to determine the total possible number of groups for a given number of items.

Syntax

COMBIN(number,number_chosen)

The COMBIN function syntax has the following arguments:

• number Required. The number of items. This value cannot be less than zero. It must be greater or cannot be less than number_chosen.
• number_chosen Required. The number of items in each combination. This value cannot be less than zero and cannot be greater than number.

Notes

• Numeric arguments are truncated to integers (for example, 6.8 truncates to 6).
• An error occurs if either argument is nonnumeric.
• An error occurs if either argument is less than 0 or if number is less than number_chosen.
• Unlike a permutation, a combination is any set or subset of items, regardless of their internal order.
• The number of combinations is as follows, where number = n and number_chosen = k.

Example

Calculate number of possible teams that may be created from a set of Candidates.

Description

Joins several text items into one text item.

Syntax

CONCAT(text1,text2,...)

The CONCAT function syntax has the following arguments:

• text1 Required. A field identifier, literal string number, or formula. Numeric values are converted to text.
• text2,... Optional. A field identifier, literal string number, or formula.

Notes

• Enclose literal strings between quotation marks. Numbers do not require quotation marks
• To insert a space in a string, use double quotation marks with a space between them (" ").
• Use "" to insert a double quotation mark within a literal string.
• This function does not ignore empty elements.

Example

Use the following formula to assemble the full name from its parts:

CONCAT([First]," ",[Middle]," ",[Last1]," ",[Last2])

Typically, the middle name is optional and the second last name is either omitted or not used in a region, so we can use a formula to omit the space before missing values:

=CONCAT([First],IF(ISBLANK([Middle]),," "),[Middle]," ",[Last1],IF(ISBLANK([Last2]),," "),[Last2])

Description

Joins several text items into one text item.

Syntax

The CONCATENATE function syntax has the following arguments:

• text1 Required. A field identifier, literal string number, or formula. Numeric values are converted to text.
• text2,... Optional. A field identifier, literal string number, or formula.

Example

Use the following formula to assemble the full name from its parts:

CONCATENATE([First]," ",[Middle]," ",[Last1]," ",[Last2])

Typically, the middle name is optional and the second last name is either omitted or not used in a region, so we can use a formula to omit the space before missing values:

=CONCATENATE([First],IF(ISBLANK([Middle]),," "),[Middle]," ",[Last1],IF(ISBLANK([Last2]),," "),[Last2])

Description

Returns the cosine of a number.

Syntax

COS(number)

The COS function syntax has the following arguments:

• number Required. The angle in radians for which you want the cosine.

Note

• If the angle is in degrees, either multiply the angle by PI()/180 or use the RADIANS function to convert the angle to radians.

Example

Description

Returns the hyperbolic cosine of a number.

Syntax

COSH(number)

The COSH function syntax has the following arguments:

• Number Required. Any real number for which you want to find the hyperbolic cosine.

Note

The hyperbolic cosine is the even part of the exponential function:

Example

COSH(EXP(1))

Description

COUNT([Column1],[Column2],[Column3],[Column4])

Syntax

COUNT(value1,value2, ...)

The COUNT function syntax has the following arguments:

• value1 Required. Reference to a field.
• value2, ... Optional. Additional references to fields.

Example

Description

Counts how many fields are not empty.

Syntax

COUNTA(value1,value2, ...)

The COUNTA function syntax has the following arguments:

• value1 Required. Reference to a field.
• value2, ... Optional. Additional references to fields.

Notes

• The COUNTA function counts cells containing any type of information, including error values and empty text (""). For example, if referenced fields contain a formula that returns an empty string, the COUNTA function counts that value. The COUNTA function does not count empty fields.
• If you do not need to count logical values, text, or error values (in other words, if you want to count only fields that contain numbers), use the COUNT function.
• If you want to count only fields that meet certain criteria, use the COUNTIF function.

Example

Description

Counts the number of blank fields.

Syntax

COUNTBLANK(range)

The COUNTBLANK function syntax has the following argument:

• range Required. Range of one or more fields in which to count the blank fields. Every field in a range must be specified.

Note

• A range specifies multiple fields in a single argument. The order does not have to match the order that columns appear in the sample data table. For example, this range specifies four columns (1, 3, 5, and 7):

  COUNTBLANK([column7]:[column5]:[column3]:[column1])

  .

Example

Description

Returns the sequential serial number that represents a particular date.

Syntax

DATE(year,month,day)

The DATE function syntax has the following arguments:

• year Required. The value of the year argument can include one to four digits. Data Integrity Suite interprets the year argument according to the date system your computer is using. By default, this is the 1900 date system, which means the first date is January 1, 1900.
  • If year is between 0 and 1899 (inclusive), the value is added to 1900 to calculate the year. For example, DATE(108,1,2) returns January 2, 2008 (1900+108).
  • An error is generated if year is less than 0 or is 10000 or greater.
  Tip: Use four digits for the year argument to prevent unwanted results. For example, "07" could mean "1907" or "2007." Four digit years are explicit.
• month Required. A positive or negative integer representing the month of the year from 1 to 12 (January to December).
  • If month is greater than 12, that number of months is added to the first month in the specified year.
  • If month is less than one, that number of months plus 1 is subtracted from the first month in the specified year. For example DATE(2008,-3,2) returns the serial number representing September 2, 2007.
• day Required. A positive or negative integer representing the day of the month from 1 to 31.
  • If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. For example, DATE(2008,1,35) returns the serial number representing February 4, 2008.
  • If day is less than 1, day subtracts the magnitude that number of days, plus one, from the first day of the month specified. For example, DATE(2008,1,-15) returns the serial number representing December 16, 2007.

Note

• Dates are stored as sequential serial numbers so that they can be used in calculations. January 1, 1900 is serial number 1, and July 16, 1969 is serial number 25400 because it is 25,400 days after January 1, 1900. The number format must be set to Date in output column to display a proper date. An output column set to Integer will show the sequential serial number.

Example

Description

Converts a date in the form of text to a serial number. This function is useful to convert dates to a format that can be sorted, formatted, or used in date calculations.

To view a date serial number as a date, you must apply the date format to the output column.

Syntax

DATEVALUE(date_text)

The DATEVALUE function syntax has the following argument:

• date_text Required Reference to a column that contains dates in date format.

Description

Converts a serial number to a day of the month.

Syntax

DAY(serial_number)

Example

Description

Calculates the number of days between two dates based on a 360-day year. The 360-day year is used in some accounting calculations based on twelve 30-day months.

Syntax

DAYS360(start_date,end_date,[method])

The DAYS360 function syntax has the following arguments:

• Start_date, end_date Required. The two dates between which you want to know the number of days. If start_date occurs after end_date, the DAYS360 function returns a negative number. Dates should be entered by using the DATE function, or derived from the results of other formulas or functions. For example, use DATE(2008,5,23) to return the 23rd day of May, 2008. Problems can occur if dates are entered as text.
• Method Optional. A logical value that specifies whether to use the U.S. or European method in the calculation.

  Method	Defined
  FALSE or omitted	U.S. (NASD) method. If the starting date is the last day of a month, it becomes equal to the 30th day of the same month. If the ending date is the last day of a month and the starting date is earlier than the 30th day of a month, the ending date becomes equal to the 1st day of the next month; otherwise the ending date becomes equal to the 30th day of the same month.
  TRUE	European method. Starting dates and ending dates that occur on the 31st day of a month become equal to the 30th day of the same month.

Example

Description

Converts radians to degrees.

Syntax

DEGREES(angle)

The DEGREES function syntax has the following argument:

• angle Required. The angle in radians to convert to degrees.

Example

Description

Returns the sum of squares of deviations from a sample mean.

Syntax

DEVSQ(number1, [number2], ...)

The DEVSQ function syntax has the following arguments:

• number1, number2, ... The first argument is required. Subsequent numbers are optional. Arguments for which to calculate the sum of squared deviations.

Notes

• Each argument can reference a single column or a range of columns. A range must specify all columns to include in any order.
• Referenced fields that contain text, logical values, or are empty cells, are ignored.
• Referenced field that contain the value zero are included.
• Error values or text that cannot be translated into numbers cause errors.
• The equation for the sum of squared deviations is:
  DEVSQ = Σ(x – x)²

Example

Description

Returns a number corresponding to an error type.

Syntax

ERROR.TYPE(error_val)

Example

Description

Rounds a number up to the nearest even integer.

Syntax

EVEN(number)

The EVEN function syntax has the following argument:

• number Required. The value to round.

Notes

• If a number is nonnumeric, EVEN returns an error.
• Number is rounded up from zero regardless of the sign of the number.

Example

Description

Checks to see if two text values are identical. Returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive.

Syntax

EXACT(text1,text2)

The EXACT function syntax has the following arguments:

• text1 Required. The first text string.
• text2 Required. The second text string.

Example

Description

Returns the mathematical constant e raised to the power of a given number. The constant e is approximately equal to 2.71828182845904, the base of the natural logarithms. It is also the limit of (1 + 1/n)ⁿ as n approaches infinity.

Syntax

EXP(number)

The EXP function syntax has the following argument:

• number Required. The exponent applied to the base e.

Notes

• To calculate powers of other bases, use the exponentiation operator (^).
• EXP is the inverse of LN, the natural logarithm of number.

Example

Description

Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...*number

Syntax

FACT(number)

The FACT function syntax has the following argument:

• number Required. A nonnegative number for which to calculate the factorial. If number is not an integer, it is truncated.

Example

Description

Returns the logical value FALSE.

Note

• You can also type the word FALSE directly into the formula, which Data Integrity Suite interprets as the logical value FALSE.

Syntax

FALSE()

The FALSE function syntax has no arguments.

Example

Description

Finds one text value within another (case-sensitive). The function returns the number of the starting position of the first text string from the first character of the second text string.

Syntax

FIND(find_text, within_text, [start_number])

• find_text Required. The text to be found.
• within_text Required. The text to be search.
• start_number Optional. Specifies the character position at which to start the search, starting with 1. The default if omitted is 1.

Notes

• This function may not be available for all languages.
• FIND is intended for use with languages that use the single-byte character set (SBCS).
• FIND always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.

Examples

Adromeda Galaxy

Description

Formats a number as text with a fixed number of decimals.

Syntax

FIXED(number, [decimals], [no_commas])

The FIXED function syntax has the following arguments:

• number Required. The number you want to round and convert to text.
• decimals Optional. The number of digits to the right of the decimal point. If you omit this argument, the default is 2.
• no_commas Optional. A logical value that, if TRUE, prevents FIXED from including commas in the returned text.

Notes

• Numbers can never have more than 15 significant digits, but decimals can be as large as 127.
• If decimals is negative, number is rounded to the left of the decimal point.
• If no_commas is FALSE or omitted, then the returned text includes commas.

Example

Description

Rounds a number down, toward zero.

Syntax

FLOOR(number,significance)

The FLOOR function syntax has the following arguments:

• number Required. The numeric value you want to round.
• significance Required. The multiple to which you want to round.

Notes

• If either argument is nonnumeric, FLOOR returns an error.
• If number is positive and significance is negative, FLOOR returns an error.
• If the sign of number is positive, a value is rounded down and adjusted toward zero. If the sign of number is negative, a value is rounded down and adjusted away from zero. If number is an exact multiple of significance, no rounding occurs.

Example

Description

Returns the future value of an investment based on a constant interest rate. You can use FV with either periodic, constant payments, or a single lump sum payment.

Syntax

FV(rate,nper,pmt,[pv],[type])

The FV function syntax has the following arguments:

• rate Required. The interest rate per period.
• nper Required. The total number of payment periods in an annuity
• pmt Required. The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument.
• pv Optional. The present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument.
• type Optional. The number 0 or 1 and indicates when payments are due. Set this to 0 if at the beginning of the period. Set this to 1 if at the end of the period. If type is omitted, it is assumed to be 0.

Description

Returns the geometric mean of a range of positive data. For example, you can use GEOMEAN to calculate average growth rate given compound interest with variable rates.

Syntax

GEOMEAN(number1, [number2], ...)

The GEOMEAN function syntax has the following arguments:

• number1 , number2, ... The first argument number1 is required, subsequent numbers are optional. 1 to 255 arguments for which you want to calculate the mean. You can use a range of columns in place of any argument.

Notes

• If an array or reference argument contains text, logical values, or empty fields, those values are ignored; however, fields with the value zero are included.
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• If any data point ≤ 0, GEOMEAN generates an error.
• The equation for the geometric mean is.
• A range specifies multiple fields separated by colons in a single argument in any order.

Example

Description

Converts a serial number to an hour. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

Syntax

HOUR(serial_number)

The HOUR function syntax has the following argument:

• Serial_number Required. The time that contains the hour you want to find. Times may be entered as text strings within quotation marks (for example, "6:45 PM"), as decimal numbers (for example, 0.78125, which represents 6:45 PM), or as results of other formulas or functions (for example, TIMEVALUE("6:45 PM")).

Note

• Time values are a portion of a date value and represented by a decimal number (for example, 12:00 PM is represented as 0.5 because it is half of a day).

Example

Description

Specifies a value returned if an expression is true or false.

Syntax

IF(logical_expression,value_if_true,value_if_false)

• logical_expression The logical expression to be evaluated by the IF statement.
• value_if_true The value, reference, or expression if True.
• value_if_false The value, reference, or expression if False.

Example

The following returns "Solvent" if the value in the AccountBalance field is greater than or equal to zero or "Insolvent" if the value is less than zero.

IF([AccountBalance]>=0,"Solvent","Insolvent")

Description

Returns the interest payment for an investment for a given period. This is based on periodic, constant payments and a constant interest rate.

Syntax

IPMT(rate, per, nper, pv, [fv], [type])

The IPMT function syntax has the following arguments:

• rate Required. The interest rate per period.
• per Required. The period to find the interest. This must be in the range 1 to nper.
• nper Required. The total number of payment periods in an annuity.
• pv Required. The present value, or the lump-sum amount that a series of future payments is worth right now.
• fv Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
• type Optional. The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.
  • 0 — Payments are due at the end of the period.
  • 1 — Payments are due at the beginning of the period.

Notes

Example

Description

Returns TRUE if the value is blank.

Syntax

ISBLANK(value)

The ISBLANK function syntax has the following argument:

• value Required. The value that you want tested. The value argument can be a blank (empty field), error, logical value, text, number, or reference value, or a column reference referring to any of these.

Note

• This function returns 1 for true or 0 for false. These show as true or false in a Boolean field.
• The ISBLANK function is useful to test the contents of a field or the outcome of a calculation.

Example

Description

Returns TRUE if the value is any error value except #N/A.

Syntax

ISERR(value)

The ISERR function syntax has the following argument:

• value Required. The value that you want tested. The value argument can be a blank (empty field), error, logical value, text, number, or reference value, or a column reference referring to any of these.

Note

• This function returns 1 for true or 0 for false. These show as true or false in a Boolean field.

Example

Description

Returns TRUE if the value is any error value.

Syntax

ISERROR(value)

The ISERROR function syntax has the following argument:

• value Required. The value that you want tested. The value argument can be a blank (empty field), error, logical value, text, number, or reference value, or a column reference referring to any of these.

Note

• This function returns 1 for true or 0 for false. These show as true or false in a Boolean field.

Example

Description

Returns TRUE if the value is a logical value.

Syntax

ISLOGICAL(value)

The ISLOGICAL function syntax has the following argument:

• value Required. The value that you want tested. The value argument can be a column reference or formula.

Example

Description

Returns TRUE if the value is the #N/A error value.

Syntax

ISNA(value)

The ISNA function syntax has the following argument:

• value Required. The value that you want tested. The value argument can be a blank (empty field), error, logical value, text, number, or reference value, or a column reference referring to any of these.

Example

Description

Returns true if the value is not text. This his function returns true for values in a non-String. Note that it returns true for empty cells.

Syntax

ISNONTEXT(value)

The ISNONTEXT function syntax has the following argument:

• value Required. The value that you want tested. The value argument can be a column reference or formula.

Description

Returns TRUE if the value is a number.

Syntax

ISNUMBER(value)

The ISNUMBER function syntax has the following argument:

• value Required. The value that you want tested. The value argument can be a blank (empty field), error, logical value, text, number, or reference value, or a column reference referring to any of these.

Example

Description

Returns TRUE if the value is text.

Syntax

ISTEXT(value)

The ISTEXT function syntax has the following argument:

• value Required. The value that you want tested. The value argument can be a blank (empty field), error, logical value, text, number, or reference value, or a column reference referring to any of these.

Example

Description

Returns the k-th largest value in a data set. If n is the number of columns in a range, LARGE(range,1) returns the largest value and LARGE(range,n) returns the smallest value in a row.

Syntax

LARGE(range,k)

The LARGE function has the following arguments.

• range Required. Range of columns in the format [column1]:[column2] ... :[columnN].
• k Required. The position (from the largest) in the array or cell range of data to return.

Notes

• All columns to include in the calculation must be specified in a range. Columns can be in any order.
• If range is empty, LARGE returns the #NUM! error value 6.
• If k ≤ 0 or if k is greater than the number of data points in a range, then large returns the #NUM! error value 6.

Example

Description

Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).

Syntax

LN(number)

The LN function syntax has the following argument:

• number Required. The positive real number for the natural logarithm.

Notes

• LN is the inverse of the EXP function.
• The argument number ≤ 0 returns the #NUM! error value 6. The natural logarithm is only defined for positive values.

Example

Description

Returns the logarithm of a number to a specified base (default is 10).

Syntax

LOG(number,[base])

The LOG function syntax has the following arguments:

• Number Required. The positive real number for which you want the logarithm.
• Base Optional. The base of the logarithm. If base is omitted, it is assumed to be 10.

Example

Description

Returns the base-10 logarithm of a number.

Syntax

LOG10(number)

The LOG10 function syntax has the following argument:

• Number Required. The positive real number for which to find the base-10 logarithm.

Example

Description

Converts text to lowercase. For example,

LOWER([LastName])

Syntax

LOWER(text)

Example

Description

Returns the maximum value in a set of values.

Syntax

MAX(number1, [number2], ...)

The MAX function has the following arguments

• number1 Required. Value, column, or field to be evaluated.
• number2, ... Optional. Subsequent values, columns, or ranges to be evaluated.

Notes

• Arguments can be numbers, column references or ranges of columns. A range specifies multiple fields in any order.
• If an argument is an array or field, only numbers in that array or field are used. Empty cells, logical values, or text in the array or field are ignored.
• If the arguments contain no numbers, MAX returns 0 (zero).
• Arguments that are error values or text that cannot be translated into numbers cause errors.
• If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the MAXA function.

Example

Description

Returns the maximum value in a list of arguments, including numbers, text, and logical values.

Syntax

The MAXA function has the following arguments

• value1 Required. Value, column, or range to be evaluated.
• value2, ... Optional. Subsequent values, columns, or ranges to be evaluated.

Notes

• Arguments can be the following: numbers; column names, ranges. Values in referenced fields can be numbers, text representations of numbers, or logical values.
• A range specifies multiple fields separated by colons in a single argument. Every field to be included must be specified. For example, [column1]:[column3]:[column4] specifies three columns.
• Logical values and text representations of numbers in the list of arguments are counted.
• If an argument is an range or a single field, only values in that range or field are used. Empty fields and text values are ignored.
• Arguments that cannot be translated into numbers cause errors.
• Boolean arguments that contain true evaluate as 1. Boolean arguments that contain false evaluate as 0.
• If the arguments contain no values, MAXA returns 0 (zero).
• If you do not want to include logical values and text representations of numbers in a reference as part of the calculation, use the MAX function.

Example

Description

Returns the matrix determinant of an array.

Syntax

MDETERM(array)

Example

Description

Returns the median of the given numbers. This function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution.

Syntax

MEDIAN(number1, [number2], ...)

The MEDIAN function has the following arguments

• number1 Required. The first number for the median.
• number2,... Optional. Subsequent numbers for the median.

Notes

• If there is an even number of numbers in the set, then MEDIAN calculates the average of the two numbers in the middle. See the second formula in the example.
• Arguments can be numbers, or fields or ranges of fields that contain numbers. A range specifies multiple fields separated by colons on a single argument.
• Logical values and text representations of numbers in the list of arguments are counted.
• If a range or field reference in an argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
• Arguments that cannot be translated into numbers cause errors.

Example

Description

Returns a specific number of characters from a text string starting at the position you specify.

Syntax

MID(text, start_num, num_chars)

Example

Description

Returns the minimum value in a list of arguments.

Syntax

MIN(number1, [number2], ...)

Example

Description

Returns the smallest value in a list of arguments, including numbers, text, and logical values.

Syntax

MINA(value1,[value2],...)

Example

Description

Converts a serial number to a minute.

Syntax

MINUTE(serial_number)

Example

Description

Returns the matrix inverse of an array.

Syntax

MINVERSE(array)

Example

Description

Returns the internal rate of return where positive and negative cash flows are financed at different rates.

Syntax

MIRR(values, finance_rate, reinvest_rate)

Example

Description

Returns the matrix product of two arrays.

Syntax

MMULT(array1,array2)

Example

Description

Returns the remainder from division.

Syntax

MOD(number,divisor)

Example

Description

Returns the most frequently occurring number in a numeric data set.

Syntax

Example

Description

Converts a serial number to a month.

Syntax

MONTH(serial_number)

Example

Description

Returns the error value #N/A.

Syntax

NA()

Example

Description

Reverses the logic of its argument.

Syntax

NOT(logical_expression1)

Example

Description

Returns the serial number of the current date and time.

Syntax

NOW()

Example

Description

Returns the number of periods for an investment.

Syntax

NPER(rate,pmt,pv,[fv],[type])

Example

Description

Returns the net present value of an investment based on a series of periodic cash flows and a discount rate.

Syntax

NPV(rate,value1,[value2],...)

Example

Description

Rounds a number up to the nearest odd integer.

Syntax

ODD(number)

Example

Description

Returns TRUE if any argument is TRUE.

Syntax

OR(logical_expression1,logical_expression2,...)

Example

Description

Returns the value of π (or pi).

Syntax

PI()

Example

Description

Returns the periodic payment for an annuity.

Syntax

PMT(rate, nper, pv, [fv], [type])

Example

Description

Returns the Poisson distribution.

Syntax

POISSON(x,mean,cumulative)

Example

Description

Returns the result of a number raised to a power.

Syntax

POWER(number,power)

Example

Description

Returns the payment on the principal for an investment for a given period.

Syntax

PPMT(rate, per, nper, pv, [fv], [type])

Example

Description

Multiplies the arguments.

Syntax

PRODUCT(number1,[number2],...)

Example

Description

Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter, such as a space. Converts all other letters to lowercase.

The following expression capitalizes the first letter of each name in a concatenated full name.

PROPER([FirstName]&" "&[MiddleName]&" "&[LastName])

Syntax

PROPER(text)

Example

Description

Returns the present value of an investment.Returns the present value of an investment.

Syntax

PV(rate, nper, pmt, [fv], [type])

Example

Description

Converts degrees to radians.

Syntax

RADIANS(angle)

Example

Description

Returns a random number between 0 and 1.

Syntax

RAND()

Example

Description

Returns the interest rate per period of an annuity.

Syntax

RATE(nper, pmt, pv, [fv], [type], [guess])

Example

Description

Replaces characters in text.

Syntax

REPLACE(old_text, start_num, num_chars, new_text)

Example

Description

Repeats text a given number of times.

Syntax

REPT(text,number_times)

Example

Description

Returns the rightmost characters from a text value.

Syntax

RIGHT(text,[num_chars])

Example

Description

Converts an Arabic numeral to Roman, as text.

Syntax

ROMAN(number, [form])

Example

Description

Rounds a number to a specified number of digits. This should not be confused with formatting to a specified decimal places. For example, ROUND([AngularVelocity],3).

Syntax

ROUND(number, number_digits)

The ROUND function syntax has the following arguments:

• number Required. The number to round.
• number_digits Required. The number of digits to which you want to round the number argument.

Notes

• If number_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.
• If number_digits is 0, the number is rounded to the nearest integer.
• If number_digits 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.

Examples

The following examples show various results for values in the Number field.

Description

Rounds a number down, toward zero. ROUNDDOWN behaves like ROUND, except that it always rounds a number down.

Syntax

ROUNDDOWN(number, number_digits)

The ROUNDDOWN function syntax has the following arguments:

• number Required. A real number to round down.
• number_digits Required. The number of digits to which to round the number.

Notes

• If number_digits is greater than 0 (zero), then number is rounded down to the specified number of decimal places.
• If number_digits is 0, then number is rounded down to the nearest integer.
• If number_digits is less than 0, then number is rounded down to the left of the decimal point.

Examples

The following examples show various results for values in the Number field.

Description

Rounds a number up, away from zero. ROUNDUP behaves like ROUND, except that it always rounds a number up.

Syntax

ROUNDUP(number, number_digits)

The ROUNDUP function syntax has the following arguments:

• number Required. A real number to round up.
• number_digits Required. The number of digits to which to round the number.

Notes

• If number_digits is greater than 0 (zero), then number is rounded up to the specified number of decimal places.
• If number_digits is 0, then number is rounded up to the nearest integer.
• If number_digits is less than 0, then number is rounded up to the left of the decimal point.

Examples

The following examples show various results for values in the Number field.

Description

Finds one text value within another (not case-sensitive).

Syntax

SEARCH(find_text,within_text,[start_num])

Example

Description

Converts a serial number to a second.

Syntax

SECOND(serial_number)

Example

Description

Returns the sign of a number.

Syntax

SIGN(number)

Example

Description

Returns the sine of the given angle.

Syntax

SIN(angle)

Example

Description

Returns the hyperbolic sine of a number.

Syntax

SINH(number)

Example

Description

Returns the k-th smallest value in a data set.

Syntax

SMALL(array, k)

Example

Description

Returns a positive square root.

Syntax

SQRT(number)

Example

Description

Estimates standard deviation based on a sample.

Syntax

STDEV(number1,[number2],...)

Example

Description

Substitutes new text for old text in a text string.

Syntax

SUBSTITUTE(text, old_text, new_text, [instance_num])

Example

Description

Returns a subtotal of a list.

Syntax

SUBTOTAL(function_num,ref1,[ref2],...)

Example

Description

Sums its arguments.

SUM([column1],[column2],[column4])

The following sums the same range of columns specified in a single argument:

SUM([column1]:[column2]:[column3]:[column4])

Syntax

SUM(number1,number2,...)

Example

The following sums three fields in three separate arguments:

SUM([column1],[column2],[column4])

The following uses the range operator to sum the same three fields in a single argument:

SUM([column1]:[column2]:[column4])

Description

Adds the cells specified by a given criteria.

Syntax

SUMIF(range, criteria, [sum_range])

Example

Description

Returns the sum of the products of corresponding array components.

Syntax

SUMPRODUCT(array1, [array2], [array3], ...)

Example

Description

Returns the sum of the squares of the arguments.

Syntax

SUMSQ(number1, [number2], ...)

Example

Description

Returns the sum of the difference of squares of corresponding values in two arrays.

Syntax

SUMX2MY2(array_x, array_y)

Example

Description

Returns the sum of the sum of squares of corresponding values in two arrays.

Syntax

SUMX2PY2(array_x, array_y)

Example

Description

Returns the sum of squares of differences of corresponding values in two arrays.

Syntax

SUMXMY2(array_x, array_y)

Example

Description

Returns the text referred to by value.

Syntax

T(value)

Example

Description

Returns the tangent of a number.

Syntax

TAN(number)

Example

Description

Returns the hyperbolic tangent of a number.

Syntax

TANH(number)

Example

Description

Returns the Percentage Points (probability) for the Student t-distribution where a numeric value (x) is a calculated value of t for which the Percentage Points are to be computed.

Syntax

TDIST(x,deg_freedom,tails)

Example