Run one- and two-variable Multiple Operations tables

Use Data > Multiple Operations to evaluate one formula against a list or grid of alternative inputs. Point the dialog at the original model input cells, not at the headings containing the alternatives.

Build the model once

This exercise follows Apache OpenOffice Calc 4.1 desktop documentation with English menus. It is not a native execution report. Save a disposable ODS workbook and reserve clear space for the output arrays.

Enter labels Price, Unit cost, Fixed cost, Units and Profit in A1:A5. Set B1=20, B2=12, B3=300 and B4=100. Enter =(B1-B2)*B4-B3 in B5. The starting profit is 500.

You will vary Units alone first, then both Price and Units. The formula still lives in B5. Neither method requires copying a different business formula into every result cell, and neither searches for an optimal answer as Solver would.

Vary one input down a column

1. Enter Units in D1 and Profit in E1. Enter 80, 100 and 120 in D2:D4. Leave E2:E4 empty.
2. Select D2:E4, including both the alternatives and the blank result column.
3. Choose Data > Multiple Operations.
4. Set Formulas to $B$5. Set Column input cell to $B$4, the model’s Units input. Leave Row input cell empty.
5. Confirm the dialog.

Expected outputs in E2:E4 are 340, 500 and 660. For example, 80 units at a margin of 8 produce 640 before the fixed cost of 300. B4 remains the original model input; D2:D4 are substitutions used by the table.

If you selected only D2:D4, undo and recreate the table with the output column included. If all results look identical, check that the formula really references B4 and that the dialog’s Column input cell is B4, not D2.

Vary inputs across and down

Use G1:J4 for a separate two-variable table. Leave G1 blank. In H1:J1 enter prices 18, 20 and 22. In G2:G4 enter unit counts 80, 100 and 120. Leave H2:J4 empty.

Select G1:J4 and choose Data > Multiple Operations. Set Formulas to $B$5, Row input cell to $B$1, and Column input cell to $B$4. The names describe how the alternatives are arranged: prices run across one row, while unit counts run down one column. Confirm.

Units / Price	18	20	22
80	180	340	500
100	300	500	700
120	420	660	900

Check opposite corners rather than just the center. The top-left result is (18−12)×80−300=180; the bottom-right is (22−12)×120−300=900. Swapping the input-cell fields might still produce numeric answers, but they would answer the wrong question.

Change a shared assumption

Change B3 from 300 to 350. Every result should decrease by 50. E2:E4 become 290, 450 and 610; H2 becomes 130 and J4 becomes 850. The same difference across the grid is an important check that fixed cost belongs to the shared model, not a heading input.

Restore B3 to 300. Then change H1 from 18 to 19: only that price column should reflect the new margin, yielding 260, 400 and 540. Restore H1 to 18 for the final saved fixture.

Recover and preserve the table

Treat generated results as an array, not independent cells to patch manually. If the orientation or range is wrong, use Undo immediately and rebuild on an empty block. Do not cover unrelated worksheet data with a larger output selection. Keep labels outside a one-variable output selection but include the corner and both sets of headings for the two-variable selection described here.

Save, close and reopen the ODS file; verify the model and both tables. If exporting to Excel, separately check the exported model: the official guide cautions that formula locations need full definition relative to the data range. A PDF is a snapshot, not an interactive sensitivity model.

Official reference

Apache OpenOffice 4.1 Calc Guide, Chapter 9, Multiple Operations in Columns or Rows and Across Rows and Columns. Recovered guide retrieved 19 October 2026; table values are independently calculated, not native-test evidence.