Tutorials For OpenOffice: Spreadsheet Math
Image by: Sally Peterson

Spreadsheet Math

Tutorial donated by Wayne Tschirhard

The purpose of this tutorial is to explain how to use math in spreadsheets.

Addition

The + sign is used for addition. To enter the + sign, press the Shift key and the +, ,key.

  1. Click on A6.

  2. Type the number 10, then press Enter. ("10" appears in "A6" and the active cell is "A7".)

  3. Type the number 5, then press Enter. ("5" appears in "A7" and the active cell is "A8".)

  4. Type =A6+A7 (Or click the “Function”, , icon. The “=” appears in “A8'. The “Function”, , icon changes to the “Accept”, , icon. Then type "A6+A7".).

  5. Press Enter (or click the “Accept”, , icon). The answer, 15, appears in A8.

Sum (Calculate The Total Of A Column)

  1. In A1, type 5, press Enter.

  2. In A2, type 5, press Enter,

  3. In A3, type 5, press Enter,

  4. In A4, type 5, press Enter

  5. In A5, type 5, press Enter.

  6. Click In A6, the last cell in the column of numbers you want to add. ("A6" has a heavy dark border and is the "Active Cell." )

  1. Click on the Sum, Σ, ("Σ" is the mathematics symbol for sum.) icon on the Formula Toolbar just left of the Input Line The Function, , icon changes to the Accept, , icon.

  2. Click the Accept, , icon (green check mark) or press Enter. The formula, =SUM(A1:A5), appears in the A6 cell.

  3. Press Enter. The sum, 25, will appear in A6. A7 becomes the active cell.

Cell Range

In the section above, the SUM function appears as =SUM(A1:A5). The A1:A5 in the parenthesis is called a Cell Range. It is shorthand for "from A1 to A5".

Subtraction

The - sign is used for subtraction. To enter the – sign, press the key.

  1. Click on A8. To clear cell A8, press the Delete key. (The “Delete Contents” window appears.) Check the Delete all box and click OK.

  2. Type =A6-A7 (Or click the “Function”, , icon. The “=” appears in “A8" The “Function”, , icon changes to the “Accept”, , icon. Then type "A6-A7".).

  3. Press Enter (Or click the “Accept”, , icon.) The answer, 20, appears in A8.

Multiplication

The star ,*, sign is used for multiplication. To enter the * sign, press the Shift key and the number 8 key.

  1. Click on A8. To clear cell A8, press the Delete key. (The “Delete Contents” window appears.) Check the Delete all box and click OK.

  2. Type =A6*A7 (Or click the “Function”, , icon. The “=” appears in “A8”. The “Function”, , icon changes to the “Accept”, , icon. Then type "A6*A7").

  3. Press Enter (Or click the “Accept”, , icon.) The answer, 125, appears in A8.

Division

The slash, /, sign is used for division. To enter the slash, /, sign, press the key [? And /].

  1. Click on A8. To clear cell A8, press the Delete key. (The “Delete Contents” window appears.) Check the Delete all box and click OK.

  2. Type =A6/A7 (Or click the “Function”, , icon. The “=” appears in “A8'. The “Function”, , icon changes to the “Accept”, , icon. Then type "A6/A7".).

  3. Press Enter (Or click the “Accept”, , icon.) The answer, 5, appears in A8.

Average

  1. In A1, type 5, press Enter.

  2. In A2, type 5, press Enter,

  3. In A3, type 5, press Enter,

  4. In A4, type 5, press Enter

  5. In A5, type 5, press Enter.

  6. In A6, type 25, press Enter.

  7. In A7, type =Average(A1:A6), press Enter. (8.33 appears in A7)

Enter Formulas

When you type in formulas, the order of operations must be correct. The correct order is: exponentiation, multiplication, division, addition, then subtraction. Let's pretend we want to perform a calculation using the equation:

x + 10

y + 20

and suppose we entered it into Calc as follows:

  1. Select the A column. (Click on the letter "A." The whole "A "column is selected)

  2. Press Delete. (Make sure the "Delete all" box is checked.)

  3. Press OK.

  4. Click on A1.

  5. Type 20 in A1 and press Tab.

  6. Type 10 in B1 and press Tab. (Cell C1 becomes active)

  7. Type the formula =A1+10/B1 + 20.(Or click the “Function”, , icon which changes to the “Accept”, , icon. Type "A1+10/B1+20").

  8. Press Enter (Or click the “Accept”, , icon. The answer, 41, appears in C1.)

The answer should be 1, using x = 20 and y = 10, but we got 41 for an answer! Why? Because Calc interpreted the equation as:

20 + (10/10) + 20

which equals 41. If you use formulas with equations in the numerator and denominator, you need to use parenthesis () to force the spreadsheet to calculate them properly. Make the following changes:

  1. Click on C1.

  2. Click on the Input Line.

  3. Edit the formula to read =(A1+10)/(B1+20). (Click just before the A, then Press the ( key [Shift-9]. Click after the 0 in 10, then press the ) key [Shift-0]. Click just before the B, then Press the ( key [Shift-9]. Click after the 0 in 20, then press the ) key [Shift-0]).

  4. Click the Accept, , icon (green check mark.) The correct answer, 1, appears in C1.

Copy Formulas

You can copy a formula so that the formula itself is copied OR that the answer to the formula is copied.

Copy The Formula Itself

  1. Select (click on) the cell(s) that is to be copied.

  2. Right-click, then click Copy. (The material is copied to the clipboard)

  3. Select (click on) the cell where the material is to appear.

  4. Right-click, then click Paste.

Copy The Answer of a Formula

  1. Select (click on) the cell(s) that has a formula.

  2. Right-click, then click Copy. (The material is copied to the clipboard)

  3. Select (click on) the cell where the material is to appear.

  4. Right-click, then click Paste Special. (The “Paste Special” window appears)

  5. Remove the check mark at Paste all.

  6. Remove the check mark at Formulas and add a check mark at Numbers. Click OK.

Exponentiation

The number 102 is written as 10^2. To enter the ^ sign, press the Shift key and the number 6, , key.

  1. Click on A9.

  2. Type =A6^A7 (Or click the “Function”, , icon. The “=” appears in "A9". The “Function”, , icon changes to the “Accept”, , icon. Then type "A6^A7".).

  3. Press Enter (Or click the “Accept”, , icon.) The answer, 100000, appears in A8.

NOTE

Tuorials are improved by input from users. We solicit your constructive criticism.

Click here to E-mail your suggestions and comment

Tutorial edited by Sue Barron

Spreadsheet Math        02/03/08

 

Last modified: 2008-04-30 01:42 UTC
Creative Commons License
This work is licensed under a Creative Commons Attribution2.5 License.