I have sheet which calculates net amount from gross. It has multiple middle steps (like calculate insurance amount in cell C2, some tax in C3). I provide gross amount in C1, and final net amount shows in cell C10.
Conceptually it is function, because it takes one parameter, and returns value based on it. I would like to use it like a function in other sheets - something like =countNet(A1). Is it possible? I want to keep this calculations in one place instead of copying them everywhere.
Use the MULTIPLE.OPERATIONS() function as inserted by menu selection Data - Multiple operations.
The tool works well for tabular input data. For inputs not collected to a structured set, it seems that you can insert the function manually. It allows replacing two input values to arrive at a different value. Please note my remark below.
Syntax:
MULTIPLE.OPERATIONS(ResultLocation;InputLocation1;Substitution1[;InputLocation2;Substitution2])
The two parameters within square brackets are optional and define a second parameter substitution. For your case you would use only one parameter substitution, something like:
MULTIPLE.OPERATIONS(C10;C1;OtherGrossAmount)
Note that I have seen some strange behavior from this function when copied after building a formula manually. It seemed to work on the spot where it was originally entered, but made assumptions about source data that did not translate well with moves. Unable to recreate the behavior today, so it may have been a bug which is now fixed. On the other hand, there may be a good reason why the function is not documented with syntax for manual entry, but only for entry from the menu item.
thanks, that’s what I was looking for
@keme: it would be interesting to know what was the problem with MULTIPLE.OPERATIONS that you observed. I saw some questions related to misconception wrt how it operates, so maybe that was just something that needed clarification?
… interesting to know what was the problem …
I agree.
It was a while back, and most likely with OpenOffice Calc, not LO. Unable to recreate it now, in either suite.
IIRC: I tried to make a straight table with two input columns, instead of the regular “pivot” (input: top row and left column) which the M.O. dialog expects. I then adjusted relative/absolute references accordingly in my formula, and copied down. I have forgotten now whether misbehavior was apparent in the pasted formulas (addressing) or just that I got unexpected results.
Unable to reproduce the erratic behavior I mentioned previously, with any current/recent Calc incarnation. Must have been a bug which was corrected, some fluke data or dependency conflict within the spreadsheet, or just that I messed up.
My previous warning is now stricken out.