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!
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:
The HYPERLINK function creates formatted hyperlinks. The syntax is as follows:
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.
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:
We’ve increased performance by changing one of HyperFormula’s API methods,
. 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.
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