Calculation by row as opposed to by column

Question

I have encountered this problem several times. Usually when doing some type of time series calculation where each row is a new observation.

The problem is that JMP complains about calculation cycles, i.e. when a formula in column A depend on column B and a formula in column B depend on column A.

However, in the cases I care about; the formula in column A depend on the cell in column B in the row above, and the formula in column B depend on the cell in column A in the row above. Hence, there is no calculation cycles.

I attached an example of what I mean. The relevant columns are Volume 2 and Conc.

The only workaround I have come up with is scripting all the calculations, one row at the time. But this may be a very big task to solve what seems like a small problem.

I there anyway I can do this in the data table without scripting?

If not, there really should be!

Calc by row example.jmp

JesperJohansen · Answer

Thank you for you reply.

I have used the solution you suggest several times before. The two main problems with it is that:

* Communicating a script, is usually much harder than just a set of formulas, which are shown in reader friendly format in the Formula editor.
* Many times, the data tables evolve over time. I don’t start out scripting simple calculations. By the time I realize that scripting may be the better approach, scripting everything may quite a large task.

Furthermore, the calculations I am trying to do are in themselves not that complicated. It seems like the real problem is that I am trying to make JMP do something it was never really designed to do. It is the last area I find that Excel still has an advantage over JMP...

I have been suggested two workarounds, do calculations in a single column or script the calculations – both things I already knew and have used. I was hoping I had overlooked some feature of JMP that would not require me to do a workaround at all. It seems like that feature does not exist. It that really true?

gzmorgan0 · Answer

Scripting a formula is pretty simple. See the attached table. I removed the formulas and attached the script.

You can add more rows, or anytime you want a recalculation, just click on the table script (in JMP 13) or select Run Script (earlier versions of JMP).

Right click or select edit script to see the simple code.  To me the script is easier to read and manage. If I had 10 columns of interdependent formulas, it would be easier to change the script than change 10 column formulas.

Calc by row example_modified.jmp

JesperJohansen · Answer

Please see my reply above.

I some simpler cases I have used your solution. However, I have encountered several cases where this is not practical, or where it requires me to ”hide” intermediate calculation steps in a single column.

So, to answer your question, I have tried it and it can solve some problem, but it does not solve my overall problem.

What I am looking for is a way to do calculations in a table by row, as opposed to by column. Or at least a way to get JMP to perform calculations where formula columns depend on each other, but individual cell do not. A way that does not involve error messages.