Choose Language Hide Translation Bar
Highlighted
JesperJohansen
Level IV

Calculation by row as opposed to by column

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!

BR
Jesper
6 REPLIES 6
Highlighted
txnelson
Super User

Re: Calculation by row as opposed to by column

Would it be helpful if once you have created the new columns with the formulas, that you then just remove the formulas and leave the static calculated values? 

dt:Volume 2 << delete formula;
dt:Conc << delete formula;
Jim
Highlighted
JesperJohansen
Level IV

Re: Calculation by row as opposed to by column

Unfortunately, not.

 

Maybe my attached example was a bit too simple, but may times my tables contains tens of formula columns and thousands of rows.

Many different rows may depend on the detected calculation cycle. And the cycle itself may involve many more than two columns.

My problem is that the calculations are halted by the error message. Sometimes I can get the calculations done by rerunning the formulas several times, and accepting the cycle warnings, but in more complex examples this may require accepting the error message MANY times – too many to be practical. And even then, I may need to rerun the formulas many times to be sure all columns are correct.

So what I am looking for is a way to get the calculations performed in the data table – without error messages, and without having to do (much) scripting.

BR
Jesper
Highlighted
KarenC
Super User

Re: Calculation by row as opposed to by column

Does this work for you? Calculate directly wihtin the Conc column.

 

Highlighted
JesperJohansen
Level IV

Re: Calculation by row as opposed to by column

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.

BR
Jesper
Highlighted
gzmorgan0
Super User

Re: Calculation by row as opposed to by column

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. 

Highlighted
JesperJohansen
Level IV

Re: Calculation by row as opposed to by column

Thank you for you reply.

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

  1. Communicating a script, is usually much harder than just a set of formulas, which are shown in reader friendly format in the Formula editor.
  2. 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?

BR
Jesper
Article Labels

    There are no labels assigned to this post.