Introduction

Apache OpenOffice 4.1 Calc Guide

Chapter 9: Data Analysis

< Previous Section

Next Section >

• Introduction
• Consolidating Data
• Creating Subtotals
• Using “What If” Scenarios
  • Creating Scenarios
  • Settings
  • Changing Scenarios
    • Changing Scenario Properties
    • Changing Scenario Cell Values
  • Working with Scenarios Using the Navigator
  • Tracking Values in Scenarios
• Using Other “What If” Tools
  • Multiple Operations in Columns or Rows
    • Calculating with One Formula and One Variable
    • Calculating with Several Formulas Simultaneously
  • Multiple Operations Across Rows and Columns
    • Calculating with Two Variables
• Working Backwards Using Goal Seek
  • Goal Seek Example
• Using the Solver
  • Solver Example

< Previous Page

Next Page >

Introduction

In addition to its other features, Calc offers several tools that can help you quickly manipulate the information in your spreadsheets. These tools include such tasks as copying and reusing data, creating subtotals automatically, and varying information to help you find the answers you need. All these are divided between the Tools and Data menus.

To a newcomer to spreadsheets, these tools may at first seem overwhelming. If you do not have an immediate need for one of the tools, it is best to focus on what the tool does at a high level and not worry about memorizing the details of its use. When you have a specific problem to solve, you can then work through the details. None of the tools is difficult to use, but even an experienced spreadsheet user is unlikely to remember the details of every tool.

The tools described in this chapter are:

• Consolidate Data — takes groups of similar data and makes a new data set by combining the values from the original groups using functions such as SUM or AVERAGE. The combination can be by position (e. g. combining all the first rows, all the second rows, etc) or by matching labels (e. g. combining rows if they are assigned to the same region or person).
• Subtotals — sorts data by as many as three labels and calculates a value, such as a sum or average, based on all of the values in each group of labels. For example, if data are labeled with the name of a person and a day of the week, you can calculate the sum for each combination of a person and day of the week.
• Scenario — stores a version of a calculation so you can have several Scenarios stored and quickly examine different versions. Imagine you have a budget with several sources of income and expenses. You could make Scenarios for variations in both incomes and expenses and easily examine and discuss each case.
• Multiple Operations — allows you to define a set of values for one or two quantities that go into a calculation and display the calculation's resulting value for each input value. For example, if you calculate a cost that depends on the weight and the length of an item, you could set weights of 1, 2, 3, and 4 grams and lengths of 0.5, 1, 1.5, and 2 cm and get the resulting cost for every combination of the weights and lengths.
• Goal Seek — works backwards from a goal that you set to find the input value that produces the goal. Imagine you have a complex calculation, and you want to know what input value produces a certain final value. You could manually adjust the input value until you get close enough to the desired output. Goal Seek automates that process.
• Solver — maximizes, minimizes, or meets a goal with the result of a calculation while respecting constraints that you define. Let's say you have a $1000 credit at a company, and you want to spend it on three products: A, B, and C. The products cost $27.45, $13.87, and $54.19, respectively. You have to buy at least 10 of A, 5 of B, and 7 of C to meet your current needs. Your constraints are the minimum number of each item you must buy and the fact that you can only purchase whole numbers of each item. How many of each should you buy to spend exactly $1000? Solver will show you that you can buy 12 of A, 21 of B, and 7 of C to achieve your goal.

If your spreadsheet use is simple, you don't need to master Calc's tools. However, as your data manipulation becomes more sophisticated, those tools can save time when making calculations, especially when you must deal with hypothetical situations. Just as significantly, Calc's tools can help you preserve your work and share it with others —or yourself at a later session.

One function tool not mentioned here is called Pivot Tables. This topic is sufficiently complex that it requires a separate chapter. (See Chapter 8 PivotTables.)

The dialogs used with these tools can block your view of the spreadsheet when you want to select cells with the mouse. To select cells, start by clicking the Shrink/Maximize icon beside the input field to temporarily reduce the size of the tool’s window. That allows you to see the spreadsheet underneath and select the cells required.