OpenOffice.org OpenOffice - 3.3 Základní průvodce stáhnout pdf (Strana 249)

Using other “what if” tools

Like scenarios, Data > Multiple Operations is a planning tool for “what if”

questions. Unlike a scenario, the Multiple Operations tool does not present the

alternate versions in the same cells or with a drop-down list. Instead, the Multiple

Operations tool creates a formula array: a separate set of cells showing the results of

applying the formula to a list of alternative values for the variables used by the

formula. Although this tool is not listed among the functions, it is really a function

that acts on other functions, allowing you to calculate different results without having

to enter and run 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 of the original values.

Once the alternative values are created, you use the Multiple Operations tool to

specify which formulas you are using, as well as the original values used by the

formulas. The second array is then filled with the results of using each alternative

value in place of the original values.

The Multiple Operations tool 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, so that they 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.

You can also make formula arrays easier to work with if you apply some simple design

logic. Place the original and the formula array close together on the same sheet, and

use labels for the rows and columns in both. These small exercises in organizational

design make working with the formula array much less painful, particularly when you

are correcting mistakes or adjusting results.

Note

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.

Multiple operations in columns or rows

In your spreadsheet, enter a formula to calculate a result from values that are stored

in other cells. Then, set up a cell range containing a list of alternatives for one of the

values used in the formula. The Multiple Operations command produces a list of

results adjacent to your alternative values by running the formula against each of

these alternatives.

Chapter 9 Data Analysis 249

1 2 ... 244 245 246 247 248 249 250 251 252 253 254 ... 433 434

Komentáře k této Příručce

Žádné komentáře

OpenOffice.org OpenOffice - 3.3 Základní průvodce Strana 249

Komentáře k této Příručce

Související produkty a manuály pro Software OpenOffice.org OpenOffice - 3.3