Advanced 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 >

Advanced Functions

Like other spreadsheet programs, OpenOffice Calc can be enhanced by user-defined functions or add-ins. You can set up user-defined functions either by using the Basic IDE or by writing separate add-ins or extensions.

The basics of writing and running macros is covered in Chapter 12 (Calc Macros). Macros can be linked to menus or toolbars for ease of operation or stored in template modules to make the functions available in other documents.

Calc Add-Ins are specialized office extensions that can extend the functionality of OpenOffice with newly built-in Calc functions. Writing add-ins requires knowledge of the C++ language and the OpenOffice SDK, and is for experienced programmers. More information is available on the OpenOffice wiki page at Simple_Calc_Add-In. Several extensions for Calc have been written, and these can be found on the extensions site at https://extensions.openoffice.org/. Refer to Chapter 14 (Setting up and Customizing Calc) for more details.