[Tutorial] Order of Operations in Calc

[MrProgrammer]

This table shows the order of operations in Calc formulas as determined by many tests performed on OpenOffice 3.2. I realize that this is an old release (early 2010?) however it's what I use. Others may want to test later releases of Open Office or derivatives like NeoOffice, LibreOffice, etc. I suspect all of them use the same order of operations since a change to that would cause formulas in existing sheets to (potentially) give different results.
• 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.

[MrProgrammer]

Some releases of Calc have implemented two additional, undocumented operators: AND and OR. It is risky to use undocumented features of a product. Since these operators are not described in the Open Document Format standard, the developers are free to remove them at any time and the resulting product will still comply with the standard. Also, these operators may make your spreadsheet incompatible with other spreadsheet products, for example Google Docs. The AND() and OR() functions are part of the ODF standard and should be used instead of the logical AND and OR operators. These two logical operators have lower precedence than any of the operators previously listed in the tutorial. The precedence of AND is the same as the precedence of OR. They are evaluated left to right. They have the additional quirk that the operator must be followed by a left parenthesis, so =A2=A3 AND (A4)>A5 is allowed but =A2=A3 AND A4>A5 is not. The safe way to write this formula is using the function: =AND(A2=A3;A4>A5).

The semicolon(;) is not a Calc operator. It is a separator for:
• Multiple parameters in a function — IF(M2=5;D5;P4+1)
• Terms (numbers or text) in an array constant — {3;1;4;1;5}

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications > Calc section.