Overview of New Math and String Functions in DHTMLX Spreadsheet 4.0

DHTMLX Spreadsheet is a comprehensive JavaScript spreadsheet control intended for presenting data in Excel-like tables and easily editing and formatting the necessary aspects on-the-fly. It allows adding the spreadsheet functionality to any part of your application and can be very useful in preparing various types of analytical documentation and storing tabular data in a well-structured way.

Recently, our development team has presented the major update of the DHTMLX Spreadsheet component to version 4.0. The release comes with a variety of new features such as support of TypeScript type definitions, the possibility to freeze columns, and numerous improvements to the current feature set of our JS library.

But most importantly, the new version includes a range of built-in math formulas and string functions that are compatible with Excel and Google Sheets. And in this article, we are going to review this primary novelty in more detail.

Math Formulas

It is hard to imagine any financial reports or budget forecasts prepared in spreadsheets without math formulas allowing you to perform various arithmetic operations. At the same time, the capabilities of modern web business spreadsheets should not be limited to common mathematical formulas.

Therefore, starting from version 4.0, DHTMLX Spreadsheet enables you to utilize a pack of math functions for specifying relations between values in particular cells, making calculations with these values, and obtaining exact results.

To facilitate easier comprehension, let us consider the most notable functions and their purpose in groups.

• Basic Arithmetic Operations

The ability to perform basic arithmetic calculations is a must for manipulating numerical data within your project. Thus, our JS component includes such popular functions as SUM for adding up values, PRODUCT for multiplying given numbers, QUOTIENT for dividing integers without the remainder, and POWER that calculates a certain number, raised to a required power.

• MIN, MAX, and AVERAGE Functions

When it comes to managing statistical data or preparing statistics reports, then it may be hard for you to go without special math functions such as MIN, MAX, and AVERAGE that are intended for these goals. Using DHTMLX Spreadsheet in your app, you can easily calculate the smallest, largest or average number in an array of values.

• Rounding Functions

It is often the case that when you examine web spreadsheet reports with a lot of numerical data, you may want to deal with clear round numbers. The use of decimals can ensure absolute precision, but sometimes these extra numbers can make reports harder to read or communicate the info in a clear form. So, you can consider utilizing rounding functions.

The group of math formulas for rounding numbers is presented in DHTMLX Spreadsheet v4.0. They help to round values up or down to a given number of decimal places.

• Counting Functions

Business people who use web spreadsheets for managing their daily activities may find it necessary to count the number of cells containing various types of data. For this purpose, our JavaScript control includes three counting functions.

For instance, if an accountant of a big company compiles a document containing calculated salaries of a large staff, it may be helpful to use COUNT function to make sure that the corresponding array/column is filled with numerical data. To exclude the possibility of error messages or blank cells in large tables with calculations, you can apply other counting functions for displaying cells with non-numerical data (COUNTA) or cells without data at all (COUNTBLANK).

On the top of that, the package also includes a group of complex math functions such as PI, SQRT, STDEVP, SUMPRODUCT, SUMSQ, and VARP, which give you more useful options for managing spreadsheets.

String Functions

Most often, business spreadsheets focus on numbers handling features, but sometimes you may need to work with documents that contain a lot of textual information and extra text formatting options may come in handy. That is why our major update also includes various string functions.

Utilizing these functions, you can present text data exactly the way you want to see it. For example, it is possible to show text from two or more cells in one cell using CONCATENATE function, capitalize letters (UPPER) or adhere only to lowercase characters (LOWER), remove extra spaces between words (TRIM), and perform many other useful tricks.

All in all, the latest version of DHTMLX Spreadsheet is enriched with 25 math formulas and 9 string functions. Here is a special sample prepared by our development team where you can play around with all the functions.

For those web developers who are looking for an Excel-like spreadsheet component, here is a free 30-day trial version of DHTMLX Spreadsheet.

If you want to become familiar with other formulas included in the release, learn more about the functions mentioned above and get insight into other new features, read the release article on our blog.