The Fx() function allows Excel functions to behave like Quintessence functions. When an Excel array function is passed to the Fx() function, it automatically expands into its array constituents.

Syntax

Fx (Value)

Note:

Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.

Examples

Example 1. Use Fx to iteratively evaluate every line in a range

Example 2. Using Fx with the ‘Sparse’ parameter to control layout

Example 3. Using Fx to expand an array for use by the Excel Linest() function

Note:

How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.

Example 1. Using Fx to iteratively evaluate every line in a range

Without the Fx function

The goal is to categorise the value of the various asset holdings, where a value less than 1000000 is considered ‘Low’ and anything equal to or above 100000 is ‘High’. Without using the Fx function, only the first row is evaluated by the IF function, despite referencing the array B2 to B8.

With the Fx function

By using the Fx function, the entire range (B2:B8) is evaluated by the IF function, resulting in the correct output.

Download this example

Example 2. Using Fx with the ‘Sparse’ parameter to control layout

The Fx() function can be used to prevent data from being overwritten by setting the Sparse parameter to TRUE. In this example, the AlignToAxes() function is used in a report which contains predefined formulas. The AlignToAxes() function is passed to the Fx() function. Fx() is provided with an additional parameter (Sparse = TRUE) which instructs it to retain internal unmatched information within the output block. Therefore the first row of totals is not overwritten.

Function call in cell ***:

=Fx(AlignToAxes(A2:D10, F3:G7, H2:I2, "none", "C1,C3", "C2", "C4"), TRUE)

If the Fx() function had not been used, or Sparse had not been set to TRUE, the internal unmatched information would have been overwritten, in this case the first row of Totals.

Download this example

Example 3. Using Fx to expand input into an array for use by the Excel Linest() function

In the first function in this example, Linest() only returns one value, which is incorrect. This is because the function is not treating the input ranges as arrays.

In Excel, a function can be made to recognise an input range as an array by selecting the output range and then typing CTRL+SHIFT+ENTER. This places brackets around the function {} as shown below. The size of the output range needs to be known in advance.

By enclosing the Linest() function in the Fx() function, the range is expanded into an array, without the need to type CTRL+SHIFT+ENTER. The result is the same, but there is no need to select the output range.

Download this example