HyperFormula 2.5.0: New functions and performance improvements

Mathilda Hartnell HyperFormula / May 31, 2023

HyperFormula 2.5.0: New functions and performance improvements

We’ve just put the finishing touches on HyperFormula 2.5.0. This version introduces three new functions: IFS, HYPERLINK, and ADDRESS. Additionally, we’ve made significant performance improvements.

New functions: IFS, HYPERLINK, and ADDRESS

HyperFormula boasts an impressive collection of built-in functions — nearly 400 of them. This update adds three new entries to the catalog: IFS, HYPERLINK, and ADDRESS. These additions come to us courtesy of Chris Thilgen — thanks a lot!

IFS

The IFS function checks whether one or more conditions are true. Subsequently, it returns a value corresponding to the first TRUE condition. The IFS function can replace nested IF statements for cleaner, more readable formulas.

The syntax is as follows:

IFS(Condition1, Value1, [Condition2, Value2], [Condition_n, Value_n])

As a quick example, see the following demo. The Grade column calculates grades based on the amounts of points listed in the Score column:

HYPERLINK

The HYPERLINK function creates formatted hyperlinks. The syntax is as follows:

=HYPERLINK(url, [link_label])

The url is a web address to link to, while link_label is an optional label. For example, the following formula creates a hyperlink to the HyperFormula docs:

=HYPERLINK("https://hyperformula.handsontable.com/", "HypeFormula docs")

HyperFormula is a headless product: it doesn’t have its own user interface, as it’s meant to be a part of another application. Thus, HYPERLINK allows it to create valid hyperlinks within that external application, fully following its formatting conventions.

ADDRESS

Finally, there’s ADDRESS. This function returns a cell reference as a string value. For example, =ADDRESS(5,23) will return the address for the fifth row in the 23rd column: $E$23.

Additionally, using the optional parameters, you can specify whether the function returns relative or absolute addresses, whether it uses A1 or R1C1 notation, and which sheet will be used.

The full syntax looks like this:

=ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])

Try out the ADDRESS function along with its optional parameters, using the following demo. See how you can specify absolute and relative addresses separately for columns and for rows:

Performance improvements

We’ve increased performance by changing one of HyperFormula’s API methods, updateConfig(). Now, the method rebuilds the engine only if the configuration file is changed. Because rebuilding is computationally expensive, performing it less often makes HyperFormula faster.

Changed SEARCH behavior

Lastly, we changed the SEARCH behavior. In compliance with the OpenFormula standard, searching will now always be case-insensitive, regardless of the configuration.

Release notes

What we added

  • Added a new function: ADDRESS. #1221
  • Added a new function: HYPERLINK. #1215
  • Added a new function: IFS. #1157

What we changed

  • Optimized the updateConfig() method to rebuild HyperFormula only when the new configuration is different from the old one. #1251

What we fixed

  • Fixed the SEARCH function to be case-insensitive regardless of HyperFormula’s configuration. #1225