If you try to combine features of a full-grown calculation engine with the flexibility of modern-day JavaScript, you may end up developing… HyperFormula.

A huge library of functions

Looking back, even we at Handsontable are quite amazed to see how large the library of supported functions has grown. We currently stopped at 389 functions, in 16 languages. But, who knows, the number may still go up.

Most likely, you’ll never need all of those functions at once. But, there’s a high chance that HyperFormula supports exactly those few Excel functions that will save you eons of time when building your app.

For the full list of all supported functions, check the HyperFormula documentation. To get a quick glimpse of what HyperFormula has to offer—read on.

Note: HyperFormula is a headless engine (it doesn’t confine you to any specific UI), but we’ll employ an impromptu UI structure to help you imagine what those functions may work like for your end users.

1. Binary number conversions

Among the nearly 50 of HyperFormula’s engineering functions, some are dedicated to converting binary numbers to decimals, octals, and hexadecimals.

For example, to convert a binary number to a decimal, you can use the BIN2DEC function, the same way you’d do it in Excel or Google Sheets: BIN2DEC(binary_number).

Similarly to BIN2DEC, you can use a whole bunch of other functions to convert numbers from and into the binary notation:

Function	Conversion	Syntax	Example
BIN2DEC	Binary to decimal	BIN2DEC(binary_number)	=BIN2DEC(1010111)
BIN2HEX	Binary to hexadecimal	BIN2HEX(binary_number; optional_max_number_of_characters_to_display)	=BIN2HEX(1010111,20)
BIN2OCT	Binary to octal	BIN2OCT(binary_number; optional_max_number_of_characters_to_display)	=BIN2OCT(1010111,20)
DEC2BIN	Decimal to binary	DEC2BIN(decimal_number_between-512_and_+511; optional_max_number_of_characters_to_display)	=DEC2BIN(1,20)
HEX2BIN	Hexadecimal to binary	HEX2BIN(binary_number; optional_max_number_of_characters_to_display)	=HEX2BIN(1010111,20)
OCT2BIN	Octal to binary	OCT2BIN(binary_number; optional_max_number_of_characters_to_display)	=OCT2BIN(1010111,20)

 

2. RADIANS and DEGREES

So, you can easily convert number notations—what about units?

Let’s take measuring angles. Ever heard of Excel’s RADIANS and DEGREES functions? They’re in HyperFormula, too:

• RADIANS(number_of_degrees)
• DEGREES(number_of_radians)

For example:

• =RADIANS(360)
• =DEGREES(6.28)
• =DEGREES(3.14)

3. ROMAN and ARABIC

Speaking of conversions, there’s a neat pair of Excel functions that lets you convert roman numbers into arabic numbers, and vice versa. Quite intuitively, they’re called ROMAN and ARABIC. In case you wondered, HyperFormula has got that covered, too:

• ROMAN(arabic_number)
• ARABIC(roman_number)

For example:

• =ROMAN(1)
• =ROMAN(5)
• =ARABIC(I)
• =ARABIC(V)

4. EOMONTH

Another function that doesn’t lack fans among Excel or Google Sheets users is EOMONTH. Again, HyperFormula reconstructs that function in JavaScript, and lets you tweak it whatever way you like.

By default, EOMONTH calculates the last day of the month that comes a number of months after (or before) the date of your choice:

• EOMONTH(start_date;number_of_months_from_the_start_date)

For example, to calculate the last day of the month that will come 10 months from now, you can use: =EOMONTH(NOW(),10).

As in Excel, the default output of EOMONTH is a serial number (a number of days passed since January 1, 1900). And, as in Excel, you can easily convert it to a human-readable date, thanks to functions such as DAY, MONTH, or YEAR.

5. FACT and FACTDOUBLE

If your end users are into computing factorials, HyperFormula lets you use the two Excel factorial functions, FACT and FACTDOUBLE:

• FACT(decimal_number)
• FACTDOUBLE(decimal_number)

For example:

• =FACT(0)
• =FACT(5)
• =FACTDOUBLE(0)
• =FACTDOUBLE(5)

As the names suggest, FACT calculates a given number’s factorial, and FACTDOUBLE calculates its double factorial:

6. COUNT, COUNTA, COUNTIF functions

HyperFormula supports more than 130 Excel functions related to statistics. Among them, you’ll find a group of simple yet frequently-used functions that help you see what’s what in a large pool of data. The most basic one of them is COUNTA.

COUNTA takes a given set of values (a list of arguments, or an array of cells) and counts how many of those values are not empty. In other words, it counts how many values there are in a given set of data:

• COUNTA(set_of_values)

As you can imagine, it’s a perfect match for HyperFormula’s array features:

• =COUNTA(A1:B4)
• =COUNTA(A1:D400)

You can also do the opposite: count how many cells in your set are empty. Just use the COUNTBLANK function—you’ll find it in HyperFormula as well:

• COUNTBLANK(set_of_values)

For example:

• =COUNTBLANK(A1:B4)
• =COUNTBLANK(A1:D400)

Now, a similar function called COUNT also gives you a count of values in a given area, but it focuses solely on numbers. It counts how many numbers (as opposed to other types of values) are in a given set of values:

• COUNT(set_of_values)

For example:

• =COUNTBLANK(A1:B4)
• =COUNTBLANK(A1:D400)

If you can get the count of non-empty values, the count of empty values, and the count of number values, then you can also get the count of… any values you want, because HyperFormula supports the massively popular COUNTIF Excel function.

COUNTIF takes a set of values, and counts how many values in that set match your custom criteria: COUNTIF(range_of_values; criteria).

For example:

• =COUNTIF(A2:A10,">5")
• =COUNTIF(A2:B40,"John Doe")
• =COUNTIF(A2:C9,A1)

But that’s not all: COUNTIFS, the Excel function that can apply multiple criteria to multiple sets of values is supported in HyperFormula as well: =COUNTIFS(Range1; Criterion1 [; Range2; Criterion2 [; ...]])

For example: =COUNTIFS(A1:B5,"=Yes",B1:B5,"=No").

7. NETWORKDAYS

There’s a simple Excel function that helps you count how many working days there are between two dates. It’s called NETWORKDAYS, and you’ll find it in HyperFormula as well:

• NETWORKDAYS(start_date;end_date;holiday_dates)

NETWORKDAYS lets you specify custom holidays as well, for example:

• NETWORKDAYS(12/30/2020,06/28/2021;05/05/2021,04/06/2021)

But there’s more: HyperFormula supports NETWORKDAYS.INTL, which lets you set your own definition of a working day (for example, you can set Sunday as a working day, and Friday as a weekend day).

8. SUMPRODUCT

Another simple Excel function that often comes in handy is SUMPRODUCT. It takes arrays of values (as many as you like), multiplies those arrays’ corresponding values, and sums up the products of those multiplications.

You’ll find SUMPRODUCT in HyperFormula as well:

• SUMPRODUCT(array1;array2...array30)

For example:

• SUMPRODUCT(A1:A4;B1:B4)

One function to rule them all

Now, if you’re building an advanced business app, there’s always a chance that this one particular Excel function that you need badly is missing from HyperFormula’s huge library. Or, it may even turn out that your end users’ needs are so specific that they can’t be met by any existing function. What then?

The answer is simple: you are free to completely remake any of HyperFormula’s built-in functions. For example, you can take a built-in function and make it compatible with other spreadsheet software, like Google Sheets, LibreOffice Calc, or Gnumeric, if that’s your requirement. Or, you can remove some functions that you don’t need. Or, you can completely remove all the built-in functions and implement only those that match your app’s exact needs.

But your ultimate weapon is creating your own custom function from scratch. HyperFormula lets you easily create functions that do exactly what you need them to. You can even add your own translations for them. In short, the sky is the limit.

Of course, the functions we mentioned in this article are just a tiny sample of what HyperFormula has to offer. In the near future, we’re going to devote some more blog posts to other Excel functions that HyperFormula supports—stay tuned!

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.