🎉 HyperFormula 2.0.0: reversed ranges and expanded whitespace character support

[AMBudnik]

HyperFormula 2.0.0 is here! This version adds support for reversed ranges, lets you expand the scope of whitespace characters allowed, drops the GPU.js dependency, and improves the behavior of the RATE function.

Reversed ranges

From the formula authoring perspective, reversed ranges are the most important feature of this release.

So far, you could reference a range of cells in only one direction, for example:

• =A1:B2
• =A:B (entire columns)
• =1:2 (entire rows)
• =Sheet1!A1:Sheet5!B2 (across different sheets, so-called 3D reference. It’s fun!)

Starting with HyperFormula 2.0.0, you can reference ranges in any direction. The table below shows a few examples of reversed ranges allowed:

Allowed before	Allowed now
=A1:B2	=A1:B2 =B2:A1 =A2:B1 =B1:A2
=A:B	=A:B =B:A
=1:2	=1:2 =2:1
=Sheet1!A1:Sheet5!B2	=Sheet1!A1:Sheet5!B2 =Sheet5!B2:Sheet1!A1

This feature complies with the OpenFormula standard.

Expanded whitespace character support

HyperFormula 2.0.0 lets you easily expand the scope of whitespace characters allowed within formulas.

By default, HyperFormula is compliant with the OpenFormula standard in this respect. This means that the only whitespace characters allowed are the following:

• SPACE (U+0020)
• CHARACTER TABULATION (U+0009)
• LINE FEED (U+000A)
• CARRIAGE RETURN (U+000D)

Starting with HyperFormula 2.0.0, you can also allow all types of whitespace characters captured by JavaScript’s \s character class. Just set the new ignoreWhiteSpace configuration option to 'any':

const engine = HyperFormula.buildFromArray([[]], {
  licenseKey: 'gpl-v3',
  ignoreWhiteSpace: 'any',
})

For example, with ignoreWhiteSpace set to 'any', you can use non-breaking spaces within your formulas, without getting an error.

Dropping the GPU.js dependency

When releasing HyperFormula 1.2.0 back in September 2021, we removed GPU.js from optional dependencies, and marked the gpujs and gpuMode configuration options as deprecated (to learn more, see the HyperFormula 1.2.0 release blog).

With HyperFormula 2.0.0, it’s time to remove the GPU.js dependency and its options entirely. While having a negligible impact on HyperFormula’s performance (and only in some rare use cases), this decision significantly shortens the installation time.

Using GPU.js was a successful experiment to speed up the calculation time of some functions (MMULT, MAXPOOL, MEDIANPOOL, and TRANSPOSE) used with very large data sets. For all users who need the GPU acceleration in their projects, it’s still available in HyperFormula 1.0.0.

RATE function fix and documentation updates

HyperFormula 2.0.0 improves the behavior of the RATE function, by adjusting its approximation level and the maximum number of iterations.

We also updated the HyperFormula documentation, adding some new content (e.g., a new section on ranges), clearing up inconsistent terminology, improving the API reference, and more.

Release notes: what we added

• Added support for reversed ranges. #834
• Added a new configuration option, ignoreWhiteSpace, which allows for parsing formulas that contain whitespace characters of any kind. #898

Release notes: what we changed

• Breaking change: Removed the gpu.js dependency and its use, to speed up the installation time. #812
• Breaking change: Removed the deprecated gpujs and gpuMode configuration options. #812

Release notes: what we fixed

• Fixed an issue where the RATE function didn’t converge for some inputs. #905