Data preparation- substract column data by other column

[#1]

Hi,

I have a table with different scan results.

I have 180 scans with 1000 results per scan.

results are at the Z column.

I want to create a new column with relative scan results, meaning the results of scan 2 - the results of scan 1, results of scan 3 - the results of scan 2, and so on.

subtraction of results must be by Location number, 1-1, 2-2, 3-3, and so on.

I've tried to transpose the data but got lost in the way.

I would appreciate any help.

[#2 · Accepted solution]

You could first split your data

Perform the calculations in the split data, stack it and then join/update it back to your original data.

Other option is to sort your data by Location# (not necessary for my formula, but easier to very if the calculation is correct) and then create a formula to calculate the differences

:Z - (Col Moving Average(:Z, 1, 1, 0, :"Location #"n) * 2 - :Z);

[#3]

Thanks for the quick replay,

this works great.

can you elaborate on the formula a bit?

what are the parameters you put in?

[#4]

Col Moving Average(name, options, <By var, ...>) should explain the parameters.

The idea is to use Col Moving Average to calculate the previous value (this is "easy" method to take groups into account). You can do this type of calculations using Dif() or Lag() but they would require additional checks for group changes (simple If is usually enough for that).

[#5]

The Scripting Index, available under the Help pull down menu has both the definition of the function and of the parameters, along with examples.