OpenOffice.Org 1.0 Resource Kit

2017-07-07 02:10:07

This section describes the basic elements used in formulas and the rules Calc uses to calculate formulas.

Add-on Program dmaths

The add-on GPL software, dmaths, can be used as an addon to OpenOffice.org or StarOffice 6.0. It helps you more easily write formulas and vectors.

See http://www.dmaths.com.

Basic Operators

Table 20-1 shows the operators (symbols that enable calculations and other operations) you'll use frequently in formulas.

=	The equals sign is the most important symbol in formulas. All formulas must begin with it. Without it, a formula is not a formula, but a text string.

Table 20-1. Basic operators you'll use in formulas

Operator	Description	Example
=	Equals sign. All formulas must begin with this.	=2+2
^	Exponent. Raises the number to the left of the operator to the power of the number on the right.	=10^2 (same as 10 ² )
*	Multiply	=5*5
/	Divide	=24/6
+	Add	=B5+12
-	Subtract	=C1-E17
<	Less than	=If(A4<45;"Buy more Sun stock";"Sell")
>	Greater than	=If(A5>45;"Sell";"Buy more Sun stock")
<=	Less than or equal to	=If(F2<=.05;"Refinance";"Don't refinance")
>=	Greater than or equal to	=If(F3>=.24;"Call to lower interest rate";"Grin and bear it")
<>	Not equal to	=If(D3<>D4;"Debits do not equal credits";"Your books balance")
:	Range of cells. Includes all cells from the cell to the left of the colon to the cell to the right of the colon.	=sum(A1:A25) (Adds up all cells from A1 to A25)
;	Non-consecutive cells and separating formula parts . Let's you include non-consecutive cells in a calculation.	=sum(A1;A7;A25) (Adds up cells A1, A7, and A25) See also the IF() formulas above. Semi-colons are used where commas are used in other spreadsheet applications.
!	Intersection	=sum(A1:B3!B2:C7) (Calculates the sum of all cells in the intersection. In this example, the result is the sum of cells B2 and B3)

Order of Evaluation for Expressions

What's the answer to the following math problem?

=5+10*2-14/2+4

If you start the problem left-to-right, the answer is 12. Calc also calculates left-to-right , but it also follows algebraic ordering rules for the order it calculates: it multiplies and divides first, then adds and subtracts. With that in mind, let's simplify the problem by solving the multiplication and division parts first:

=5+20-7+4

Then handle the addition and subtraction, working left-to-right, and the answer is 22.

Exponents

If you include exponents in your formulas (for example, 10 ² , which is written as 10^2 in Calc), those are calculated before multiplication and division.

Using Parentheses to Control Calculation Order

You can exercise more control over the calculation order by using parentheses. This is also an algebra thing. Calc solves formulas within parentheses first (using algebraic ordering within the parentheses) before it solves the rest of the formula.

Using the previous formula as an example, Table 20-2 shows how using parentheses in different ways can produce different solutions.

Table 20-2. Using parentheses to control calculation order

Formula	Solution
=(5+10)*2-(14/2+4)	19
=5+(10*2-14)/2+4	12
=(5+10*2)-14/(2+4)	22.67
=5+10*(2-14/2)+4	-41

Inevitably as you use parentheses, you'll need to control calculation order further by nesting parentheses within parentheses. For example:

=(9*(10-7))/((8*3)-(7*3))+10^2

Calc solves formulas the inner parentheses first. The solution to this formula is 109 (10^2 is the same as 10 ² , or 100).

Since most of your formulas will contain cell references (rather, should contain cell references), creating a calculation order with parentheses can get tricky, because you're constantly trying to see which cell references represent which numbers. For example, =(A7*(SUM(B5:B7)))/D2. There are a couple of tricks you can use to help visually map cell references to the numbers they reference:

Even if you're not finished with your formula, press Enter to save the formula in its current state. Most likely you will get an error message in the cell, which is okay.

Double-click the cell. Calc color codes the cell references in the formula and highlights the referenced cells in with corresponding colors. This helps you edit the formula.

Use the trace precedents and dependents feature to have arrows point back and forth from the cell containing the formula to all the referenced cells. See Tracing Precedents , Dependents, and Errors on page 587.