Not long after releasing HyperFormula 1.0.0, we’re adding another feature: from now on, the calculateFormula method supports the array arithmetic mode introduced in version 1.0.0. We also fixed a few bugs along the way.

What is the calculateFormula method?

The calculateFormula method lets you calculate a formula without entering it into a sheet.

Why would you use it? Because it lets you perform complex calculations outside of your HyperFormula sheets. For example, you could add a Calculate button that calculates any typed-in formula string. Or, you can calculate formulas living outside of your sheets, for example, in a text block.

In other words, calculateFormula lets you use the entire library of HyperFormula’s built-in functions (and any custom function you build on your own) without being bound to your spreadsheet.

How do you use calculateFormula? All you need to do is:

• Add any proper formula (starting with =)
• Specify the context in which you want your formula calculated (i.e. a sheet ID)

For example:

const hfInstance = HyperFormula.buildFromSheets({
 Sheet1: [['1', '2', '3'], ['4', '5', '6']],
 Sheet2: [['58']],
});

// returns the calculated formula's value
// for this example, returns [['11', '12', '13'], ['14', '15', '16']]
const calculatedFormula = hfInstance.calculateFormula('=A1:B3+10', 0);

What is the array arithmetic mode?

The array arithmetic mode lets you use the array features introduced in HyperFormula 1.0.0.

It lets you operate on arrays of values, similarly to Google Sheets and Microsoft Excel, which means that you can:

• Perform arithmetic operations on arrays, just like on single values
• Pass arrays to functions that would normally accept single values
• Filter arrays, with the FILTER function
• Constrain arrays’ sizes, with the ARRAY_CONSTRAIN function

You can either enable the array arithmetic mode locally with the ARRAYFORMULA function, or enable it globally with the useArrayArithmetic configuration option.

Want to learn more? Check out the HyperFormula documentation.

What does 1.1.0 change in calculateFormula?

The big news in HyperFormula 1.1.0 is that now you can use the mentioned array features within the calculateFormula method.

For example:

• hfInstance.calculateFormula("=ARRAYFORMULA(ISEVEN(A1:D200*10))", sheetId));
• hfInstance.calculateFormula("=ARRAYFORMULA(OCT2DEC(A1:D200))", sheetId));
• hfInstance.calculateFormula("=ARRAYFORMULA(ROUNDUP(A1:D200,1))", sheetId));

This opens up new possibilities: you can perform complex calculations on entire arrays of values, outside of your HyperFormula sheets.

Release notes: what we changed

• binarySearchThreshold option is now deprecated, as every search of sorted data always uses binary search. (#791)

Release notes: what we added

• Added support for array arithmetic in temporary formulas. (#782)
• Added new CellType returned by getCellType, CellType.ARRAYFORMULA, assigned to top-left corner of arrays. Recognized by isCellPartOfArray and doesCellHaveFormula. (#781)

Release notes: what we fixed

• Fixed issue with searching sorted data. (#787)
• Fixed destroy method to properly destroy HyperFormula instances. (#788)

Acknowledgments

The HyperFormula project was co-financed from European Union funds under the European Regional Development Funds as a part of the Smart Growth Operational Programme. Project implemented as a part of the National Centre for Research and Development: Fast Track.