11 new functions have been added to the Calculate control:
XOR
The XOR function returns the exclusive OR for the given arguments.
Syntax
=XOR(logical_value1, logical_value2, …)
Example
=XOR(5>0,7<9) outputs FALSE
IFNA
The IFNA function returns the value specified if the formula returns the N/A error value; otherwise, it returns the result of the given formula.
Syntax
=IFNA(Formula_value, value_if_na)
Example
=IFNA("N/A", "Incorrect") outputs Incorrect
CLEAN
The CLEAN function is used to remove non-printable characters from the given text, represented by numbers 0 to 31 of the 7-bit ASCII code.
Syntax
=CLEAN(text)
Example
=CLEAN(Syncfusion) outputs Syncfusion
ISREF
The ISREF function returns the logical value TRUE if the given value is a reference value; otherwise, the function returns FALSE.
Syntax
=ISREF(given_value)
Example
=ISREF("Region1") outputs FALSE
AVERAGEIF
The AVERAGEIF function calculates the average of values in a given array that satisfy a specific criteria.
Syntax
=AVERAGEIF(range, criteria, average_range)
range: Array of values to be tested against a given criteria.
criteria: The condition to be tested for in each of the values in the given range.
average_range: Specific set of values to evaluate against the provided criteria and then average. If one is not provided, range is used instead.
AVERAGEIFS
The AVERAGEIFS function calculates the average of values in a given array that satisfy a set of criteria.
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )
criteria_range1, criteria_range 2: Arrays of values to be tested against the given criteria.
criteria1, criteria2: The conditions to be tested in each of the values of given range.
average_range: Specific set of values to be averaged if the criteria range meets the provided criteria.
NETWORKDAYS
The NETWORKDAYS function is used to calculate the number of whole workdays between two given dates. This includes all weekdays from Monday to Friday, but excludes a supplied list of holidays.
Syntax
=NETWORKDAYS(start_date, end_date, [holidays])
start_date: The start of the period to find the workdays.
end_date: The end of the period to find the workdays.
[holidays]: An optional argument specifying an array of dates that are not to be counted as work days.
Example
=NETWORKDAYS(DATE(2012,10,1), DATE(2013,3,1)) outputs 110
SUMIFS
The SUMIFS function finds the sum of values in a given array that satisfy a set of given criteria.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
criteria_range1: Array of values to be tested against the given criteria.
criteria1: The condition to be tested for in each of the values in the given range.
sum_range: The range of values to be summed if the associated criteria range meets the specified criteria.
ADDRESS
The ADDRESS function returns the address of a cell in a worksheet based on specified row and column numbers.
Syntax
=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
row_num: A numeric value that specifies the row number.
column_num: A numeric value that specifies the column number.
abs_num (optional): A numeric value that specifies the type of reference to return.
A1: A logical value that specifies the A1 or R1C1 reference style.
Example
=ADDRESS(2, 3, 2, FALSE) outputs R2C[3]
LOOKUP
The LOOKUP function returns a value either from a one-row or one-column range, or an array. The LOOKUP function has two syntax forms: vector and array.
Vector Form: The vector form of LOOKUP looks in a one-row or one-column range for a value, and then returns a value from the same position in a second one-row or one-column range.
Syntax
=LOOKUP(lookup_value, lookup_vector, result_vector)
Array form: The array form of LOOKUP looks in the first row or column of an array for the specified value, and then returns a value from the same position in the last row or column of the array.
Syntax
=LOOKUP(lookup_value, array)
Example
=LOOKUP("C", {"a","b","c","d";1,2,3,4}) outputs 3
SEARCH
The SEARCH function returns the location of a substring in a string. This function is not case-sensitive.
Syntax
=SEARCH(substring, string, [start_position])
Example
=SEARCH("base","database") outputs 5
