Understanding Functions

Apache OpenOffice 4.1 Calc Guide

Chapter 7: Formulas and Functions

< Previous Section

Next Section >

• Introduction
• Setting Up a Spreadsheet
  • The Trap of Fixed Values
  • Lack of Documentation
  • Error-checking Formulas
  • Appearance vs. Computation
• Creating Formulas
  • Operators in Formulas
  • Operator Types
    • Arithmetic Operators
    • Comparative Operators
    • Text Operators
    • Reference Operators
      • Range Operator
      • Reference Concatenation Operator
      • Intersection Operator
  • Relative and Absolute References
    • Relative Referencing
    • Absolute Referencing
  • Order of Calculation
  • Calculations Linking Sheets
• Understanding Functions
  • Understanding the Structure of Functions
  • Nested Functions
  • Function Wizard
  • Entering Formulas in Cells or the Input Line
• Strategies for Creating Formulas and Functions
  • Place a Unique Formula in Each Cell
  • Break Formulas into Parts and Combine the Parts
  • Use the Basic Editor to Create Functions
• Finding and Fixing Errors
  • Error Messages
    • Examples of Common Errors
    • Err:532 #DIV/0!
    • #VALUE Non-existent Value and #REF! Incorrect References
  • Color Coding for Input
  • The Detective
• Examples of Functions
  • Basic Arithmetic and Statistic Functions
    • Basic Arithmetic
    • Simple Statistics
  • Rounding off Numbers
    • Rounding Methods
• Using Regular Expressions in Functions
• Advanced Functions

< Previous Page

Next Page >

Understanding Functions

Calc includes over 350 functions that help you analyze and reference data. Many of these functions are for use with numbers, but others can be used with dates and times or even text. A function may be as simple as adding two numbers together or finding the average of a list of numbers. Alternatively, it may be as complex as calculating the standard deviation of a sample or a hyperbolic tangent of a number.

Typically, the name of a function is an abbreviated description of what the function does. For instance, the FV function gives the future value of an investment, while BIN2HEX converts a binary number to a hexadecimal number. By tradition, functions are entered entirely in upper case letters, although Calc will read them correctly if they are in lower or mixed case.

A few basic functions are somewhat similar to operators.

Examples:

+	→ This operator adds two numbers together for a result. SUM() adds individual cells or cell ranges together.
*	→ This operator multiplies two numbers together for a result. PRODUCT() does the same for multiplying that SUM() does for adding.

Each function has a number of arguments used in the calculations. These arguments may or may not have their own name. Your task is to enter the arguments needed to run the function. In some cases, the arguments have predefined choices, and you may need to refer to the online help or Appendix B (Description of Functions) in this book to understand them. More often, however, an argument is a value you enter manually or one already entered in a cell or range of cells on the spreadsheet. In other words, an argument is what the function must work on. In Calc, you can enter values from other cells by typing their name or range or by selecting cells with the mouse. If the values in the cells change, the result of the function is automatically updated.

For compatibility, functions and their arguments in Calc have almost identical names to their counterparts in Microsoft Excel. However, both Excel and Calc have functions that the other lacks. Occasionally, functions with the same names in Calc and Excel have different arguments or slightly different names for the same argument—neither of which can be imported to the other. However, most functions can be used in both Calc and Excel without any change.