Using Other “What If” Tools

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 >

Using Other “What If” Tools

Like scenarios, Data → Multiple Operations is a planning tool for “what if” questions. But unlike a scenario, the Multiple Operations tool doesn't present alternate versions in the same cells or with a drop-down list. Instead, the Multiple Operations tool creates a formula array. We call that a separate set of cells showing the results of applying a formula to a list of alternative values for the formula's variables. Even though Multiple Operations isn't listed as a function, it can be considered one. That's because it acts on other functions and thereby allows you to calculate different results without entering and running them separately.

To use the Multiple Operations tool, you need two arrays of cells. The first array contains the original or default values and the formulas applied to them. The formulas must be in a range.

The second array is the formula array. It is created by entering a list of alternative values for one or two original values.

Once alternative values are created, you then use the Multiple Operations tool to specify formulas and the original values used by the formulas. The second array is filled, or in the parlance populated, with the results of using each alternative value instead of the original values.

Multiple Operations can use any number of formulas but only one or two variables. With one variable, the formula array of alternative values for the variables will be in a single column or row. With two variables, you should outline a table of cells such that the alternative values for one variable are arranged as column headings, and the alternative values for the other variable act as row headings.

Setting up multiple operations can be confusing at first. For example, when using two variables, you need to select them carefully to form a meaningful table. Not every pair of variables is useful to add to the same formula array. Yet, even when working with a single variable, a new user can easily make mistakes or forget the relationships between cells in the original array and cells in the formula array. In these situations, Tools → Detective can help to clarify the relations.

Applying some simple design logic can make formula arrays easier to work with. For example, place the original and the formula array close together on the same sheet and use labels for both the rows and columns. These small organizational exercises make working with formula arrays much more comfortable, particularly when correcting mistakes or adjusting results.

If you export a spreadsheet containing multiple operations to Microsoft Excel, the location of the cells containing the formula must be fully defined relative to the data range.