[Tutorial] Order of Operations in Calc

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

[Tutorial] Order of Operations in Calc

Postby MrProgrammer » 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.

Operation               Type     Operator   Precedence  Operand(s)                Result                  
Identity Prefix + 10 Reference, Number, 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.
Screen Shot 2015-09-19 at 22.04.03 .png

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.
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3162
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Tutorial] Order of Operations in Calc

Postby MrProgrammer » Sun Oct 25, 2015 8:18 am

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).

Note that questions are not allowed in the Tutorials section of the forum. Ask them in the Applications > Calc section.
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.9.5.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 3162
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 3 guests