Functions

Functions

Functions can refer to built-in functions or custom functions (vba, xlm, xll, automation and javascript functions.) The primary tool for function information is the insert function dialog. Bold argument names indicate that the input is not optional. Without any input data, each argument shows a data type next to the input which may be a value (number, text, logical, any), a reference or an array type. Since arguments may consist of multiple types, an argument that shows a value as the data type may also take arrays or references and an argument that shows a reference type may also take arrays.

The evaluate formula tool breaks down the evaluation of a formula into calculation steps. Prior to evaluating the result, any arguments that require value conversion are underlined. If references are entered as inputs, only those arguments that are not reference types are converted to values when stepping through.

For example the arguments of XLOOKUP are shown in the function wizard as: any, any, number, logical. Stepping through formula evaluation with parenthesised arrays and references eg ({1}) and (A1) shows which arguments support arrays are reference types and returns. Formatting arrays

XLOOKUP