## 8 examples of useful Excel functions in HyperFormula

Features / August 19, 2021

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)`

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.