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)

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

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.

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.

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.

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.