• Operations with higher precedence are performed first.
• Operations of equal precedence are performed left to right.
• You can use parentheses to specify a different order of operations.
In the examples that follow, numbers and formulas are written for the English (USA) locale, where the decimal separator is the period.
Operation Type Operator Precedence Operand(s) Result
Identity Prefix + 10 Ref/Num/Text Same as operand
Range Infix : 9 Reference Reference
Intersection Infix ! 8 Reference Reference
Union Infix ~ 7 Reference Reference
Negation Prefix - 6 Number Number
Percent Suffix % 5 Number Number
Exponentiation Infix ^ 4 Number Number
Division Infix / 3 Number Number
Multiplication Infix * 3 Number Number
Addition Infix + 2 Number Number
Subtraction Infix - 2 Number Number
Concatenation Infix & 1 Text Text
Equality Infix = 0 Number/Text Number (FALSE=0, TRUE=1)
Greater than Infix > 0 Number/Text Number (FALSE=0, TRUE=1)
Greater than or equal Infix >= 0 Number/Text Number (FALSE=0, TRUE=1)
Inequality Infix <> 0 Number/Text Number (FALSE=0, TRUE=1)
Less than Infix < 0 Number/Text Number (FALSE=0, TRUE=1)
Less than or equal Infix <= 0 Number/Text Number (FALSE=0, TRUE=1)
The Identity operation seems to be undocumented. It returns the value to its right, whether numeric or not. The Range operator returns a reference to smallest rectangular set of cells which contains the given cells. D5:E7 is six cells, D5, D6, D7, E5, E6, and E7. The Intersection operator returns a reference to the set of cells which are common to two ranges. D5:E7!E4:E9 is the range E5:E7 since those three cells are in both D5:E7 and E4:E9. The Union operator returns a reference to multiple ranges of cells; in general only "consolidation" functions (SUM, COUNT, COUNTIF, AVERAGE, etc.) can accept multiple ranges of cells.
Addition, Subtraction, Multiplication, Division, Negation, and Exponentiation are the standard operators from mathematics. 11/4 is 2.75 so if you want only the quotient from a division use the QUOTIENT() function and if you want only the remainder from a division use the MOD() function. Unless y is an integer, x^y is evaluated as EXP(LOG(x)*y) so x must be positive. Equality, Greater than, Greater than or equal, Inequality, Less than, and Less than or equal are the relational operators, returning values representing TRUE or FALSE.
The symbol + is used for two different operations, Identity and Addition. If there's a value to its left, + signifies Addition (=A2+B2), otherwise it signifies Identity (=+A2). The symbol - is used for two different operations, Negation and Subtraction. If there's a value to its left, - signifies Subtraction (=A2-B2), otherwise it signifies Negation (=-A2). Negation reverses the sign of value to its right, so the value must be numeric. Note that the precedence for Addition is different than that for Identity, likewise for Subtraction and Negation.
Percent (%) is the only suffix operator. It divides the value on its left by 100. =(20^3)% has the value 8000÷100 or 80, though it will probably be displayed as 8000.00%. If multiple Range operators are used in a formula, Calc will simplify the formula to use only the first and last cells of the range. For example =SUM(A6:A2:A5) is accepted but stored as =SUM(A2:A6).
Suppose the formula is =2^3^2. Type that in a cell and you'll get the value 64. There are two operations in that formula, both of them Exponentiation. The leftmost one is performed first; 2 cubed is 8. The second Exponentiation squares 8 to give 64 as the value of the formula. We can tell that Calc does not compute 3 squared first (giving 9) because if it did the cell's value would be 2⁹ or 512. This test confirms that Calc performs Exponentiation left to right unless you force a different order with parentheses: =2^(3^2).
Suppose the formula is =10.5-3/2.5. Type that in a cell and you'll get the value 9.3. There are two operations, Subtraction and Division. Even though the Subtraction operator appears first, the highest precedence (3) is for Division so that's performed first. 3 divided by 2.5 is 1.2. Then the values 10.5 and 1.2 are subtracted to give the value 9.3. If Calc had performed the subtraction first, the division would give 7.5/2.5 and would have produced the value 3. This test confirms that Division has higher precedence than Subtraction.
Suppose the formula is =10.5-3&2.5. There are two operations, Subtraction and Concatenation. The highest precedence (2) is for Subtraction so that's performed first. 10.5 minus 3 is 7.5. Now the values 7.5 and 2.5 are concatenated. Concatenation requires text operands so the numeric values 7.5 and 2.5 are converted to the text values "7.5" and "2.5". These are joined to give the text value "7.52.5" which is displayed as 7.52.5 in the cell. The value in the cell is not a number. It is beyond the scope of this tutorial to discuss the details of type conversions, like number to text, in Calc.
Suppose the formula is =-5^2=0-5^2. There are 5 operations: Negation, Exponentiation, Equality, Subtraction, and Exponentiation.
• The highest precedence (6) is for Negation; the leftmost 5 is negated to become the value -5.
• The next highest precedence (4) is for Exponentiation, which occurs twice. They are evaluated left to right.
• The value -5 is squared to become the value 25. The rightmost 5 is squared to become the value 25.
• The next highest precedence (2) is for Subtraction. The values 0 and 25 are subtracted to give the value -25.
• The lowest precedence (0) is for Equality. The values 25 and -25 are compared to give the value FALSE.
The function wizard confirms the final value and the order of operations. It also shows that numeric constants in Calc are always positive; the initial - is a negation operator, and does not create a negative constant -5. If the intent was to have a formula which returns the value TRUE, you can use parentheses to change the order of evaluation: =-(5^2)=0-5^2 or =-5^2=(0-5)^2. For the former, both sides of the equality have the value -25. For the latter, both sides of the equality have the value 25. We can also use the power function instead of using parentheses: =-POWER(5;2)=0-POWER(5;2) or =POWER(-5;2)=POWER(0-5;2)
Suppose the formula is =IF(0<A2<5;"Yes";"No"). It is presumably intended to check whether A2 is between 0 and 5, but it will not work as expected. There are two operations, both Less Than. They have equal precedence so the left one is performed first. 0<A2 is either true or false. So the result of that comparison is either 1 (for true) or 0 (for false). Next we compare that result to 5. Both 0 and 1 are less than 5 so the second comparison is always true no matter what value is in A2! The value of this IF function will always be "Yes". The correct way to test if A2 is between 0 and 5 is =IF(AND(0<A2;A2<5);"Yes";"No").
This tutorial is intended to describe how Calc works. I am not a developer, so it is not the place to debate how Calc ought to work. That conversation would take place with the development team in Issue 26755 (or similar issues).
Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications > Calc section.