Choose Language Hide Translation Bar
Highlighted
Mi
Mi
Level III

Create Formula

Hello everyone,

 

how can I calculate column X_9 and X_10 with a formula?

 

For X_9:
While  X_1 = X_1 -> X_7(Row1) -X_8 = X_9

                                  X_9 - X_8

For X_10:

While X_1 = X_1 -> X_10(Row1)=X_9 - X_8
                                X_9 + X_8

 

 

Thank you very much for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Create Formula

Here is my solution:  The X__9 formula is

If( Row() == 1, sumX__8 = 0 );
If( :X__1 != Lag( :X__1 ),
	sumX__8 = 0
);
sumX__8 = sumX__8 + :X__8;
:X__7 - sumX__8;

The X__10 formula is

If( Row() == 1, theSum = 0 );
If( :X__1 != Lag( :X__1 ),
	theSum = 0
);
theSum = theSum + :X__8;
theSum;

Attached is a data table with the formulas applied

Jim

View solution in original post

9 REPLIES 9
Highlighted

Re: Create Formula

This kind of question has been asked many times. Did you search past discussions first for information that might help you solve this problem?

 

A formula is unlikely to use the While() function. You probably want to use the If() function. Also see the Lag() function.

 

Functions are documented. Please see Help > JMP Documentation LIbrary > Using JMP. There are chapters devoted to formulas and functions. Please see also Help > Scripting Index. Set this browser to display Functions (top left pull down menu) and then search for If and Lag.

Learn it once, use it forever!
Highlighted
txnelson
Super User

Re: Create Formula

I am sorry, but I can not interpret your notation on what is required for X_0 and X_10. Can you explain it in written terms?
Jim
Highlighted
Mi
Mi
Level III

Re: Create Formula

Hello,

thank for your answer.

I hope you can understand it now.

Best regards.

 

As long as the rows in column X_1 do not change
-> should be calculated in column X_9 = X_7 minus X_8.

As long as the rows in column X_1 do not change
-> column X_8 is to be added to column X_10

Highlighted
txnelson
Super User

Re: Create Formula

Here is my solution:  The X__9 formula is

If( Row() == 1, sumX__8 = 0 );
If( :X__1 != Lag( :X__1 ),
	sumX__8 = 0
);
sumX__8 = sumX__8 + :X__8;
:X__7 - sumX__8;

The X__10 formula is

If( Row() == 1, theSum = 0 );
If( :X__1 != Lag( :X__1 ),
	theSum = 0
);
theSum = theSum + :X__8;
theSum;

Attached is a data table with the formulas applied

Jim

View solution in original post

Highlighted
Mi
Mi
Level III

Re: Create Formula

Hello,
this is really wonderful!!

Thank you very much for that!

 

I hope that  I soon begin to understand how to use the formula
... and  can do it by myself.

I wish you a pleasant evening/afternoon(?).

 

Highlighted
Mi
Mi
Level III

Re: Create Formula

Hello, txnelson,

 

do I understand your formulas correctly?

 

The formula for X_9
if the Row = 1 then sum X_8 = 0
If X_1 is not equal(?!=Lag) to X_1 then sumX_8=0
otherwise sumX_8=sumX_8 + X_8

and X_7 - sumX_8

 

The formula for X_10
if Row=1 then theSum=0
if X_1 does not equal(?!=Lag) X_1

theSum=0
otherwise theSum=TheSum + X_8
Fill out theSum

Is this correctly interpreted?

 

A sunny greeting and stay healthy!

 

 

 

Highlighted
txnelson
Super User

Re: Create Formula

Your interpretation of X__9 is incorrect.  Let me attempt to explain how it works.

 

3 items need to be understood about formulas in JMP that will help with the interpretation

  1. JMP runs the formula for each row in the data table, starting with row 1.  And the function Row() will return the row that JMP is currently on
  2. You can create as many memory variables( i.e. sumX__8 ) as you want in a formula and they will hold their value until changed in the formula, from row to row.  This is one of the keys to the formula.  This variable sums up the values of X__8 from row to row, until a new X__1 value is observed.  When that happens sumX__8 is set back to 0.
  3. The result of the last calculation executed in a formula for a given row, is the value that will be displayed for the row in the data table.

So the interpretation of the formula for column X__9 is:

 

For Row 1

If Row() equals 1 then set the memory variable sumX__8 equal to 0.  This is true Row() is equal to 1 so sumX__8 is initialized to a value of 0. The memory variable sumX__8 is used to calculate the cumulative sum of X__8 within the grouping of X__1.

 

If the value of X__1 for Row 1 is not equal to the previous (Lag) value of X__1, set the value of sumX__8 to 0.  Since there is not a previous value of X__1, no calculation will be made.  sumX__8 will remain at it's current value, which is 0

 

The next line ( sumX__8 = sumX__8 + :X__8; ) is now run to set the cumulative sum for this row.  It does this by taking the current value of sumX__8, which is 0 and adds to it the X__8 value for row 0, which is 0 for the result of 0. Note: this line of code is not the "Otherwise" calculation for the previous IF() function.  This line of code is executed for every row. 

 

The next line of code( :X__7 - sumX__*) performs the calculation row 1's value of X__7, which is 20 and subtracts  the cumulative sum of X__7( sumX__8 value is 0) and comes up with a value of 20-0 or 20.  Since this is the last calculation for row 1 in the formula, this value is what ends up as row 1s value for X__9

 

Row 2

Row() is not equal to 1, so the result code of sumX__8 = 0  will not be executed

 

Row 2's value of X__1 is 1000 which is equal to the previous (Lag) value of X__1, so it;s result code will not be run

 

sumX__8 is calculated for this row, resulting is the current value of sumX__8 which is 0, added to row 2's value of X__8 which is 0 for a result of 0

 

The rows value for X__9 is calculated as row 2s value of X__7, which is 20 and subtracts the current value of sumX__8 which is 0 for a resulting value of 20.

 

This process continue from row to row.  At row 9, the first non 0 value of X__8 appears, so the value of sumX__8 becomes 0+1 for a value of 1.  Row 10 adds one more to the sumX__8, etc.

The next significant row is row 20.  During the execution of the formula for this row, the IF() function "If( :X__1 != Lag( :X__1 )" is true.  :X__1 is equal to 1001 and the previous (lag) value of X__1 is 1000, therefore the resulting code of sumX__8 gets set to 0, so the cumulative sum will start over. 

 

Your interpretation is changed to:

The formula for X_9
if the Row = 1 then sum X_8 = 0
If X_1 is not equal the previous value of X_1 then sumX_8=0
sumX_8=sumX_8 + X_8

X__9 is calculated to be X_7 - sumX_8

 

The formula for X__10 is pretty much the same as X__9, so using your interpretation, with my modifications it is

The formula for X_10
if Row=1 then theSum=0
if X_1 does not equal the previous value of  X_1 theSum=0
theSum=TheSum + X_8

X__10 is set by referencing the value of the variable theSum

 

Jim
Highlighted
Mi
Mi
Level III

Re: Create Formula

Mi_0-1589522623009.png

Mi_1-1589522837990.pngMi_2-1589522858292.png

This is wrong....

 

 

 

 

Hello I am trying to make the formula myself
How do I get the next If under it?

 

Thanks for your help.

Highlighted
txnelson
Super User

Re: Create Formula

Complex formulas are pretty tough to create using the formula creator.  So my rule of thumb, is that if you are going have multiple lines of calculations, I use the script editor within the Formula Creator.

If you double click on the box that has the wording "no formula"

noformula1.PNG

You will see an input box that will allow you to type in a single line....or a couple of lines

noformula2.PNG

But if you click on the object in the upper right corner of the box

noformula3.PNG

You will have the Script Editor displayed, where you can enter the entire script easily

noformula5.PNG

Jim
Article Labels

    There are no labels assigned to this post.