### [Tutorial] Order of Operations in Calc

Posted:

**Wed Sep 23, 2015 8:27 am**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.

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 Identity operation seems to be undocumented. It returns the value to its right, whether numeric or not. 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 "1.5". These are joined to give the text value "7.51.5" which is displayed as 7.51.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). I will request a moderator to delete any posts in this tutorial which offer arguments for why Calc should work differently than it does; they are not relevant here. However, if you believe that the order of operations listed in this tutorial is contrary to the way that Calc actually works, provide evidence to support your position (attach an ODS document, not a picture) and indicate which product and version was used so that others can test. Put the product/version in your post since your signature for the post only reflects your current status. Posts in this tutorial which offer no testable evidence might be subject to deletion.

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

• 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 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 Identity operation seems to be undocumented. It returns the value to its right, whether numeric or not. 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 "1.5". These are joined to give the text value "7.51.5" which is displayed as 7.51.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). I will request a moderator to delete any posts in this tutorial which offer arguments for why Calc should work differently than it does; they are not relevant here. However, if you believe that the order of operations listed in this tutorial is contrary to the way that Calc actually works, provide evidence to support your position (attach an ODS document, not a picture) and indicate which product and version was used so that others can test. Put the product/version in your post since your signature for the post only reflects your current status. Posts in this tutorial which offer no testable evidence might be subject to deletion.

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