Formulas
Formulas are an important component of tables, they help to automatically perform all calculations and also avoid many wasted hours, days, and sometimes months of manual work.
Fortunately, the PubyDoc DataTable and Charts plugin fully supports the formulas familiar to us from working with Excel.
And this tutorial will help you learn the formulas in more detail and get acquainted with the most popular ones.
How to find the formula editor?
To customize and manage formulas, you need to switch to Extended builder, here you will see a field for entering formulas.
The formula tells the plugin what to do with numbers, values in a cell or group of cells. Without formulas, spreadsheets are not needed in principle.
The construction of a formula includes: constants, operators, references, functions, range names, parentheses containing arguments, and other formulas. Using an example, we will analyze the practical application of formulas for novice users.
How to use formulas?
To use formulas, simple equations are used, in order to set an equation, you need to remember that:
Each column is a letter, in ascending order from A. (A; B; C… and so on)
Each column is a digit in ascending order from 0. (1;2;3… and so on)
To set formula for a cell, you must activate it (place the cursor) and enter equals (=). You can also enter an equal sign in the formula bar. After entering the formula, the result of the calculation will appear in the cell.
For convenience, you can enable the Show Column Letter option to have the column names become letters in chronological order.
The Data Table uses standard mathematical operators, just like Excel:
| Operator | Operation | Example |
| +(plus) | Addition | = B4 + 7 |
| -(minus) | Subtraction | =A9-100 |
| *(asterisk) | Multiplication | =A3*2 |
| / (slash) | Division | =A7/A8 |
| ^ (circumflex) | Degree | = 6^2 |
| = (equal sign) | Equals | |
| < | Less | |
| > | More | |
| <= | Less than or equal | |
| = | Greater than or equal | |
| <> | Not Equal | |
| % | Percent |
When you change the values in the cells, the formula automatically recalculates the result.
Example
For example, let’s multiply the cells by each other, using the same example, you can make calculations with other operators.
You can spread the formula to adjacent cells, as we showed in the example, for this:
After the value calculated by the formula appears, move the cursor to the lower right corner of the cell, click on the “black square” icon and drag it in the desired direction. In this example, down.
Or just double click on the black square icon, this will spread the formula over the column, with the corresponding argument name.
The arguments in the formula will change according to the new location. And if in the very first cell it was B1 and C1, in the last position of the column it will be already B3 and C3. Accordingly, if you start spreading formulas across rows, it will not be the numbers that will change, but the letters in the cell designations.
How to use functions?
The plugin fully supports mathematical functions, they may be familiar to you if you worked in excel.
A function in a Data Table is a predefined formula that performs calculations using given values in a specific order. You can use functions to speed up tasks, simplify formulas, and perform calculations that would be impossible without them.
Syntax of Functions in DataTable
To work correctly, a function must be written in a specific sequence called syntax. The basic syntax of a function includes an equal sign (=), the name of the function (for example, MAX), and one or more arguments. The arguments contain the information to be evaluated. In the following example, the function sums the values in the range A1:A20.
There are functions in Excel that do not contain any arguments. For example, the =TODAY() function returns the current date from your computer’s system time.
Working with Arguments
Arguments can refer to single cells or ranges of cells and must be enclosed in parentheses. Excel functions can have one or more arguments, depending on the syntax.
For example, the function =AVERAGE(C1:C6) will calculate the average value in the cell range C1:C6. This function contains only one argument.
Multiple arguments must be separated by a semicolon. For example, the function =SUM(A1:A3; C1:C2; E2) sums the values of all cells in three arguments.
Thus, you can use the formulas and functions familiar to you from excel, and simplify the work with data in the table.
If you encounter a problem or feature that doesn’t work, please contact us and we’ll be happy to help.