OpenOffice.org OpenOffice - 3.2 Guide de base télécharger pdf (Page 283)

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.

Note

Before you choose the Data > Multiple Operations option, be

sure to select not only your list of alternative values but also the

adjacent cells into which the results should be placed.

In the Formulas field of the Multiple Operations dialog, enter the cell

reference to the formula that you wish to use.

The arrangement of your alternative values dictates how you should

complete the rest of the dialog. If you have listed them in a single

column, you should complete the field for Column input cell. If they are

along a single row, complete the Row input cell field. You may also use

both in more advanced cases. Both single and double-variable versions

are explained below.

The above can be explained best by examples. Cell references

correspond to those in the following figures.

Let’s say you produce toys that you sell for $10 each (cell B1). Each toy

costs $2 to make (cell B2), in addition to which you have fixed costs of

$10,000 per year (cell B3). How much profit will you make in a year if

you sell a particular number of toys?

Calculating with one formula and one variable

1) To calculate the profit, first enter any number as the quantity

(items sold); in this example, 2000 (cell B4). The profit is found

from the formula Profit=Quantity * (Selling price – Direct costs) –

Fixed costs. Enter this formula in B5: =B4*(B1-B2)-B3.

2) In column D enter a variety of alternative annual sales figures,

one below the other; for example, 500 to 5000, in steps of 500.

3) Select the range D2:E11, and thus the values in column D and the

empty cells (which will receive the results of the calculations)

alongside in column E.

4) Choose Data > Multiple Operations.

5) With the cursor in the Formulas field of the Multiple operations

dialog, click cell B5.

Chapter 9 Data Analysis 283

1 2 ... 278 279 280 281 282 283 284 285 286 287 288 ... 496 497

Pas de commentaire

OpenOffice.org OpenOffice - 3.2 Guide de base Page 283