Working with formulas and functions
A formula is an expression for performing calculations, which always begins with an equal sign (=). Formulas can include the following elements:
- Operands: data for calculations. These are constants (numbers, text, dates) or references to cells and ranges (e.g., A1 or B2:B10).
- Operators: symbols that define the type of operation (arithmetic, comparison, text concatenation).
- Functions: predefined formulas to simplify complex calculations.
A function is a formula built into the application that performs standard calculations. Functions require arguments (input data). For example, =SUM(A1:A10) sums the values in the specified range.
Syntax and entering formulas
For formulas to work correctly, the following rules must be observed:
- A formula always begins with =.
- All opening and closing parentheses must match.
- All required function arguments must be specified.
- Constants must not contain the $ symbol.
- For the CELL and INFO functions, text parameters must be entered in English in double quotes, separated by a semicolon (for example, =CELL("contents"; A2)).
Visual highlighting
When entering a formula, references to cells and ranges are highlighted in color. The color of the border and fill corresponds to the color of the reference in the formula. The active cell (where the cursor is positioned in its address, or the cell selected on the sheet) is highlighted with a solid border, while inactive cells are highlighted with a dotted border.
Tooltips and autocomplete
When entering a formula, a drop-down list appears with suggestions: functions, named ranges, and tables. The list is sorted alphabetically. For functions with duplicate names, the address (File/Sheet) is displayed.
Viewing partial results
You can see the result of calculating part of a formula by selecting the desired fragment in the formula bar or in the cell. The result will be displayed in a pop-up window.
Quick navigation
The cell into which the formula is being entered remains visible on the sheet. You can quickly jump to it from another sheet by clicking its address in the formula or by pressing the Enter key.
Operator precedence
When calculating complex formulas, the application follows strict operator precedence rules:
- Expressions in parentheses ( ) are calculated first (innermost parentheses first).
- Then operations are performed according to their precedence (table).
- Operators with the same precedence are calculated from left to right.
Using parentheses allows you to explicitly specify the calculation order and make the formula clearer.
| Priority | Operator | Description and example |
|---|---|---|
| 1 | () | Parentheses. Define the order of calculations.=(A1+5)*B1 |
| 2 | - (unary) | Unary minus. Denotes a negative number.=-10 * A1 |
| 3 | % | Percentage calculation.=A1 * 10% |
| 4 | ^ | Exponentiation.=A1^2 |
| 5 | * and / | Multiplication and division. =A1 / B1 * 100 |
| 6 | + and - | Addition and subtraction. =A1 + B1 - C1 |
| 7 | & | Text concatenation. =A1 & " " & B1 |
| 8 | , <>, <, <=, >, >= | Comparison operators. Return TRUE or FALSE values. =A1 > B1 |
Arithmetic operators
Used for mathematical operations with numbers and data in the "Date and Time" format. If one of the operands is not a number, the result of the operation will be the #VALUE! error (table).
| Operator | Value | Example | Result |
|---|---|---|---|
| + | Addition | =6 + 7 | 13 |
| - | Subtraction/Negation | =B12 - B3 | Depends on cell values |
| * | Multiplication | =86 * 34 | 2924 |
| / | Division | =36 / 3 | 12 |
| ^ | Exponentiation | =D8 ^ 2 | Square of the value in D8 |
| % | Percentage (calculates a fraction) | 76 | 0.76 |
Tip: to extract the Nth root, use the ^ operator. For example, the square root: =36^(1/2) (result: 6).
Text concatenation operator
Combines several text strings into one (table).
| Operator | Meaning | Example | Result |
|---|---|---|---|
| & | Concatenation | ="My" & "Office" =A2 & " " & A3 | "MyOffice" Combines A2, a space, and A3 |
Comparison operators
Compare two values (numbers, dates, text) and return a logical value TRUE or FALSE (table).
| Operator | Meaning | Example | Result |
|---|---|---|---|
| = | Equal to | =6 = 6 | TRUE |
| > | Greater than | =9 > 17 | FALSE |
| < | Less than | =A2 < C3 | Depends on values |
| >= | Greater than or equal to | =0 >= 6 | FALSE |
| <= | Less than or equal to | =P12 <= 7 | Depends on value |
| <> | Not equal | =3 <> 4 | TRUE |
Reference operators
Used to work with cell ranges (table).
| Operator | Value | Example |
|---|---|---|
| :(colon) | Range operator | B1:D7 — reference to all cells from B1 to D7 |
| 'sheet_name'!(apostrophes and exclamation mark) | Reference to another sheet. A reference to a cell or range on another sheet. | =SUM(B2 + 'Sheet2'!B2) =SUM('Sheet2'!B2:C4) |
Tip: when entering formulas, select cell ranges with the mouse — this helps avoid errors in manual address entry.
Automatic calculation
For quick data analysis without entering formulas, select a range of cells. Basic statistical indicators will be automatically displayed at the bottom of the window:
- SUM — total sum of all numbers in the selected range.
- AVERAGE — arithmetic mean of all numbers in the range.
- MIN — the smallest number in the range.
- MAX — the largest number in the range.
- NUMERICAL COUNT — counts the number of cells containing numbers.
- COUNT — counts all non-empty cells in the range (including text, dates, and numbers).
The set of displayed functions can be configured using the button 
at the bottom of the window.