Difference between each points

[#1]

I would like to create a new column which shows the difference between the two values as highlighted (Column = Value). I need help to come up with a formula in that new column which shows the difference between blue point and red point for each Abs[Voltage] and for each Site and Coupon. Attaching the jmp file.

[#2]

Here is the formula that I came up with.....others may have alternate solutions.

theDif = Col Max( :Value, :Site, :"Abs[Voltage]"n ) - Col Min( :Value, :Site, :"Abs[Voltage]"n );
If( :Value < Col Mean( :Value, :Site, :"Abs[Voltage]"n ),
	theDif = theDif * -1
);
theDif;

[#3 · Accepted solution]

For formulas there are many different kinds of Col formula combinations you can use, Jim suggested one and here is another which might work (add Abs/swap order as needed)

Col Mean(If(:EPG == "TE", :Value, .), :Coupon, :Site, :"Abs[Voltage]"n) - Col Mean(If(:EPG == "TP", :Value, .), :Coupon, :Site, :"Abs[Voltage]"n);

I know you asked for a formula, but if you can split your data this is much simpler to do as you can just add new difference column as you would be comparing row by row. In this case you might want to remove duplicate values first to avoid some possible issues. Depending on your data you can do this from rows menu and select duplicates -> remove them or create new summary table.

[#4]

Using anything with COL MEAN, hangs/crashes jump often. My file has about 100k of rows. Is there any other solution which is more elegant?

[#5]

Split value by EPG grouping by site and Abs(voltage).

There will then be 2 columns.  One for TE Value and one for TP Value.

Create a new column with a formula that calculates the difference.

Finally, go back to the original data table and using Tables=>Update, join the calculated table to the original table, matching on Site and Abs(Voltage)

The difference values will be replicated to both the TE and TP rows for each Abs(voltage)/Site row.