Possible Bug in Multiple Operations

[brim2ull]

I think there is a problem with the Multiple Operations tool. Below is my simple example of the issue. However I’ll also try and describe the setup.

I have four cells in a column representing the parameters of the equation:

y = mx + c

Where M = 4, X = 2 and C is the function =RAND(). Therefore the value of Y varies each time the model is recalculated.

If a column of values for X is set-up, any value would do, and then use Multiple Operations to calculate corresponding values for Y. Nothing out of the ordinary at this point.

Next change the column of values for X to the same value and force a recalculation. NOW each of the calculated values for Y MUST be different because of the RAND() value for C. BUT their not.

Should I report this as a bug?

Multiple Operations Example.ods (18.5 KB)

[karolus]

@brim2ull

NOW each of the calculated values for Y MUST be different because of the RAND() value for C. BUT their not.

why? each MO points indirectly to the same =RAND() -calculation in C4, (which means it is linked to the existing Value, but not to sth like: evaluate the RAND-function )

[brim2ull]

Thanks karolus,

My first question relates to:

not to sth like: evaluate the RAND-function )

Sorry I don’t see the point you are trying to make here.

Next point:
It makes no sense to me that the model is not recalculated for each value provided by MO. In fact it seriously limits it usefulness. Its not possible to perform a statistical analysis using a spreadsheet model.

Finally:
How would I go about writing my own version?

Many thanks.

[karolus]

@brim2ull

How would I go about writing my own version?

Change B9 to: =RAND() and pull down.
Change C9 to: =MULTIPLE.OPERATIONS(C$6;$C$4;$B9) and pull down!

[brim2ull]

OK I understand Thank you.

Now the model is simplistic and while I appreciate that MO will support two independent variables, I could vary C and X, but that misses the point. With a complex investment model say subject to the usual market volatility, I may vary the allocation between multiple asset categories and look at the expected total return. I’m varying the the allocation not the variance which is built in to the model.

So how can I write my own version of Multiple Operations? Unfortunately it does not appear to be possible pass a cell selection to a function as a parameter.

[karolus]

you may combine MO and normal Calculations:
Multiple Operations Example combined.ods (21.1 KB)

[brim2ull]

Thanks but it does not really help so I have attached the model I have been working upon.
Financial Plan.ods (84.3 KB)