Documentation/Calc Functions/MULTIPLE.OPERATIONS
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
MULTIPLE.OPERATIONS
Category:
Non-Wizard
Summary:
Calculates a value using a sample calculation by replacing one or two variables from the sample calculation with current values.
Syntax:
MULTIPLE.OPERATIONS( Reference FormulaCell ; Reference RowCell ; Reference RowReplacement [ ; Reference ColumnCell ; Reference ColumnReplacement ] )
Returns:
Returns the value, that the sample calculation would return, if performed with the current values.
Arguments:
| FormulaCell | reference to the cell, which contains the sample calculation |
| RowCell | reference to the cell, which is used in the formula of the sample calculation for the first variable |
| RowReplacement | reference to the cell, which has the current value to be used in the sample calculation instead of the first variable |
| ColumnCell | reference to the cell, which is used in the formula of the sample calculation for the second variable |
| ColumnReplacement | reference to the cell, which has the current value to be used in the sample calculation instead of the second variable |
Additional details:
The naming “Row” and “Column” in the arguments reflects, that the function can be used to generate a two-dimensional table as it is done by the wizard in menu ▸ But in fact the current values can be located at arbitrary places.
The formula in the result cell of the sample calculation need not contain a direct cell reference for what it intended to be a “variable”. It is sufficient if the result depends indirectly on the cell of the “variable”.
A cell, which contains the current value must not depend on cells which are used in the sample calculation, neither directly nor indirectly.
The function MULTIPLE.OPERATIONS cannot take literal values as arguments, e.g. a constant number. The arguments must be references, e.g. E2 or $Sheet2.A1
It is allowed, that the mentioned references are calculated. But they must be references. Caution, the function ADDRESS returns a text, not a cell reference.
The MULTIPLE.OPERATIONS function cannot use cell ranges as arguments, even not if the function is entered as array formula.
Examples:
Example A: Using the wizard
Help pages:
https://help.libreoffice.org/latest/en-US/text/scalc/guide/multioperation.html?DbPAR=CALC
https://help.libreoffice.org/latest/en-US/text/scalc/01/12060000.html?&DbPAR=CALC
We want to make a table, that calculates the square number and cube number of {2, 3, 5, 7}
The screenshot shows the use of the wizard from ▸
Cell B3 contains the formula =B1^B2. That is the sample calculation. It contains the “variable” B1 for the base number and the “variable” B2 for the exponent.
The target range has the exponent in the row range E2:F2 and the base in the column range D3:D6.
- Mark the target area including the header as shown in the screenshot. When we have finished, cell E6 will show the value 49(=7^2) and cell F5 will show the value 125(=5^3)
- Select menu ▸ You get the dialog shown in the screenshot.
- Set cursor in field Formulas, then click on cell B3, the cell with the sample calculation. The $ signs for absolute addressing are added automatically.
- Set cursor in field Row input cell. The numbers 2 and 3 in row range E2:F2 are the numbers, that we want to use as exponent. So our 'Row input cell' is that cell, which is the exponent in the formula =B1^B2. Click on cell B2.
- Set cursor in Field Column input cell The numbers 2, 3, 5, 7 in column range D3:D6 are the numbers, that we want to use as base. So our 'Column input cell' is that cell, which is the base in the formula =B1^B2. Click on cell B1.
- You have finished. Click on OK.
Cell E6 contains now the formula =MULTIPLE.OPERATIONS($B$3;$B$1;$D6;$B$2;E$2)
Notice how the wizard has set the $ signs for absolute addressing.
The references $B$1, $B$2 and $B$3 to the sample calculation have the $ signs on row and column, because the place of the sample calculation does not change, when the formula is dragged to another place.
The reference to cell $D6 has the $ sign on the column, because the column may not change, when you drag the formula to the right. But the row needs to adapt, when you drag the formula down.
The reference to cell E$2 has the $ sign on the row, because the row may not change, when you drag the formula down. But the column needs to adapt, when you drag the formula to the right.
Example B: Enter the formula manually
We use the same data as in Example A. Instead of using the wizard, you can enter a suitable formula into cell E3 and then drag the formula to the right and down so that it covers the entire range E3:F6.
We want the row range E2:F2 to be used as exponent, so we need to pair them with cell B2 from the sample calculation, and we need to pair the column range D3:D6 with cell B1 from the sample calculation for the base.
When entering the formula in E3, row 2 needs to be fix for dragging down and column D needs to be fix for dragging to the right. The reference E$2 will traverse a range of row 2 when dragging the formula, and the reference $D3 will traverse a range of column D when dragging the formula.
You can enter in cell E3 the formula =MULTIPLE.OPERATIONS($B$3; $B$1;$D3 ; $B$2;E$2).
You can also use the formula =MULTIPLE.OPERATIONS($B$3; $B$2;E$2 ; $B$1;$D3).
Try it out!
Despite the naming in the syntax, it does not matter in which order you use 'row' or 'column'. But you only need to make sure, that the row or column reference is paired with that cell from the sample calculation, which fits in content. There are blanks added in the formula above to indicate the pairs.
Example C: Using a “variable” not in the result cell
You can use a cell of the sample calculation, which is not directly used in the formula of the result cell. In the following example the formula =B7-B10 in the result cell B11 has no direct reference to cell B5, its value depends only indirectly on cell B5. The function MULTIPLE.OPERATIONS uses the entire tree of cells, which are used to calculate the value of the result cell.
The screenshot shows the used formulas in blue. The screenshot was generated from file Media:MultipleOperationExampleWithVariableNotInResult.ods. (The topic is a typical exercise from a math schoolbook.)
Related LibreOffice functions:
ODF standard:
Related (or similar) Excel functions:
There is no (exact) equivalent function in Excel.
Data table provides similar functionality.
ToDo: How the worksheet needs to be structured, so exporting to “data table” could be possible? The help has some remarks about the topic, but is not clear.