esCalc Basics: Cells and Data Types discussed the basic data types in esCalc and mentioned the use of formulas in esCalc spreadsheet. Here we’ll explain how to use formulas in esCalc in a systematic way.
1. Calculation and edit of esCalc formulas
In esCalc, if the string in a cell starts with “=”, it will be taken as a formula to be parsed and executed after being entered. For example:
Cell value will be calculated and displayed in the cell when the formula is entered. A1’s formula is the combination of two strings. One point worth noting is that the operand on either side of the plus sign + must be the string type. The non-string type must be converted to the string type using string() function, like D1’s case. B1’s formula carries out the numeric operation. In esCalc, the calculation order is consistent with the rules of four basic arithmetic operations. So B1’s formula will be executed in the way of =(1+(4*3))-6. C1’s formula uses a function to get which year the current one is. After all these formulas are entered, cell values are:
A cell in esCalc spreadsheet has value if only it is defined as a constant cell or a calculation cell. Each cell is given its own name made from its column letter and row number. In a formula you can use the name of a cell directly to reference its cell value, like C1 used in D1’s formula. The calculation of formulas in esCalc has nothing to do with the hierarchy section on the left-most side, so hierarchy will no longer be demonstrated in the following examples.
Select a calculation cell in the cellset section and its cell value, i.e. the calculating result, will be displayed. At the same time, the formula of the calculation cell is displaying in the above formula bar:
Both the value and formula of a calculation cell are allowed to be modified. Select a cell, click on it, or on the formula bar, and you can edit the cell value, or the formula. You can press F2 to switch from one of the operations to the other.
Alternatively, you can change the formula and cell value through editing the Value and Expression in the cell property section on the right side; or just view them:
In case of the unintended data modification that occurs through operation error, esCalc provides Allow direct cell edit property. You can view or modify it by clicking Tool>Option:
If the property is not selected, direct edit in the cell is forbidden and you can only perform the edit in the formula bar or in the cell property section.
2. esCalc function options
Many esCalc functions have options. They enable functions to work in different ways. Their basic format is f@o(…) in which o is the option of function f. For example:
Formulas of A2, B2 and C2 all use interval function to calculate the interval between two dates. Without a function, A2’s value is the default days. With @y option, B2 calculates the interval of years. Being equivalent to age() function, this option can be used to calculate age. While with the @m option, C2 calculates the interval of months. Having entered the formulas, you’ll get the following cell values:
esCalc supports a great many functions to assist the calculations. For these functions and their options, please refer to Function Reference.
3. Cellset constants
Besides referencing cell values through parameters, you can set data that requires repeated use as the cellset constant and reference it by its name from the formula.
Click Tool>Cellset constant to set the cellset constants:
You can set data types for the cellset constants but are not allowed to re-assign values to them. The constants can be any data type, such as string, integer, date, and sequence. You just use their names in the formula to reference them:
Enter these formulas and you get cell values like these:
About using sequence in a formula, esCalc Basics: Sequences will cover a detailed explanation.
4. Normal calculation cells and related calculation cells
In esCalc, as soon as the formula is entered into the calculation cell, the cell value will be calculated instantly. But the calculation will only be performed once when the formula is entered, and unless the formula is modified, this calculation will not be performed again. Even if the value of the referenced cell is modified in the formula, the original value of the calculation cell will remain unchanged.
The related calculation cell, however, is a special one. It distinguishes itself from the normal calculation cell by the two equal signs == that head the formula, that is, it has one more equal sign at the beginning of the formula than the normal formula that calculates instantly. A related calculation cell calculates not only after the formula is entered or modified, but when the cell value referenced is modified. For example:
Different from C1, D1 is a related calculation cell that is marked by a small blue triangle at the lower right corner of the cell. Different cells may marked by small triangles with different colors. Once formulas for both C1 and D1 are defined, their values will be calculated and displayed in the cells:
Then if you change A1’s cell value, C1 and D1 will display different values:
Only the value of D1, the related calculation cell, has changed according to the change of the value of the referenced cell, while the value of C1, the normal calculation cell, is consistent.
The value of a related calculation cell is calculated automatically and needs no special edit. But once it is modified, the cell will become a normal calculation cell, with the two equal signs heading the formula changed to one.