Applying Multiple Operations

Multiple Operations in Columns or Rows

The Data - Multiple Operations command provides a planning tool for "what if" questions. In your spreadsheet, you enter a formula to calculate a result from values that are stored in other cells. Then, you set up a cell range where you enter some fixed values, and the Multiple Operations command will calculate the results depending on the formula.

In the Formulas field, enter the cell reference to the formula that applies to the data range. In the Column input cell/Row input cell field, enter the cell reference to the corresponding cell that is part of the formula. This can be explained best by examples:

Ekzemploj

Vi produktas ludilojn kiujn vi vendas po $10. Fari ĉiun ludilon kostas je $2. Aldone vi havas fiksitajn kostojn po $10000 jare. Kiom da profito vi ricevos jare se vi vendas specifan numeron da ludiloj?

Kalkuli per unu formulo kaj unu variablo

1. Por kalkuli la profiton, unue enigu ajnan numeron kiel la kvanton (eroj venditaj), en ĉi tiu ekzemplo 2000. La profito troveblas per la furmulo Profito = Kvanto * (Vendoprezo - Rektaj kostoj) - Fiksitaj kostoj. Enigu tiun formulon en B5.

2. In column D enter given annual sales, 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 alongside in column E.

4. Elektu je Datumoj - Pluraj operacioj.

5. Kun la kursoro en la kampo Formuloj , alklaku ĉelon B5.

6. Set the cursor in the Column input cell field and click cell B4. This means that B4, the quantity, is the variable in the formula, which is replaced by the selected column values.

7. Close the dialog with OK. You see the profits for the different quantities in column E.

Kalkuli per pluraj formuloj samtempe

1. Forigu kolumnon E.

2. Enigu la jenan formulon en C5: = B5 / B4. Vi nun kalkulas la jaran profiton por ĉiu ero vendita.

3. Select the range D2:F11, thus three columns.

4. Elektu je Datumoj - Pluraj operacioj.

5. Kun la kursoro en la kampo Formuloj , alklaku ĉelon B5.

6. Set the cursor in the Column input cell field and click cell B4.

7. Fermu la dialogon per Akcepti. Vi vidos la profitojn en kolumno E kaj la jaran profiton por ero en kolumno F.

Multiple Operations Across Rows and Columns

LibreOffice allows you to carry out joint multiple operations for columns and rows in so-called cross-tables. The formula cell has to refer to both the data range arranged in rows and the one arranged in columns. Select the range defined by both data ranges and call the multiple operation dialog. Enter the reference to the formula in the Formulas field. The Row input cell and the Column input cell fields are used to enter the reference to the corresponding cells of the formula.

Calculating with Two Variables

Consider columns A and B of the sample table above. You now want to vary not just the quantity produced annually, but also the selling price, and you are interested in the profit in each case.

Expand the table shown above. D2 thru D11 contain the numbers 500, 1000 and so on, up to 5000. In E1 through H1 enter the numbers 8, 10, 15 and 20.

1. Select the range D1:H11.

2. Elektu je Datumoj - Pluraj operacioj.

3. Kun la kursoro en la kampo Formuloj , alklaku ĉelon B5.

4. Set the cursor in the Row input cell field and click cell B1. This means that B1, the selling price, is the horizontally entered variable (with the values 8, 10, 15 and 20).

5. Set the cursor in the Column input cell field and click in B4. This means that B4, the quantity, is the vertically entered variable.

6. Close the dialog with OK. You see the profits for the different selling prices in the range E2:H11.