Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Calculation by row as opposed to by column

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Calculation by row as opposed to by column

Apr 10, 2018 11:08 AM
(3470 views)

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

Jesper

6 REPLIES 6

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jesper

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Calculation by row as opposed to by column

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jesper

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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:

- 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?

BR

Jesper

Jesper

Article Labels

There are no labels assigned to this post.