8 examples of useful Excel functions in 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
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, you can use a whole bunch of other functions to convert numbers from and into the binary notation:
||Binary to decimal||
||Binary to hexadecimal||
||Binary to octal||
||Decimal to binary||
||Hexadecimal to binary||
||Octal to binary||
2. RADIANS and DEGREES
So, you can easily convert number notations—what about units?
Let’s take measuring angles. Ever heard of Excel’s
DEGREES functions? They’re in HyperFormula, too:
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
ARABIC. In case you wondered, HyperFormula has got that covered, too:
Another function that doesn’t lack fans among Excel or Google Sheets users is
EOMONTH calculates the last day of the month that comes a number of months after (or before) the date of your choice:
For example, to calculate the last day of the month that will come 10 months from now, you can use: =
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
5. FACT and FACTDOUBLE
If your end users are into computing factorials, HyperFormula lets you use the two Excel factorial functions,
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 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:
As you can imagine, it’s a perfect match for HyperFormula’s array features:
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:
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:
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:
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: =
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 lets you specify custom holidays as well, for example:
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).
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.
SUMPRODUCT in HyperFormula as well:
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!
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.