Hello-
It looks like you possibly have a batch process, labelled by identification- where the values of MG1, MG2 are recorded at various times, each with their own row.
The first question to consider, is if you want to stay with this row structure with so many missing entries.
Consider a structure with one row per batch.
Table Menu, Summary
Group By Identification
Select MG1 and MG2, Use the Mean (which will ignore missing)
if there is potential that the data is not clean, also include the Range (investigate anything other than zero)
You will then have a summarized table by batch, which will make calculating the difference easier.
Data Table( "SAMPLE" ) << Summary(
Group( :Identification ),
Mean( :MG1 ),
Mean( :MG2 ),
Range( :MG1 ),
Range( :MG2 ),
Freq( "None" ),
Weight( "None" ),
statistics column name format( "column stat" ),
output table name( "Summary of SAMPLE" )
);
If you want to do it within the original data table, a formula column such as
Col Mean( :MG1, :Identification ) - Col Mean( :MG2, :Identification )
An alternative that only calculates when MG1 is populated for that identification and on the specific row where MG2 is located..
If( !Is Missing( :MG2 ) & !Is Missing( Col Mean( :MG1, :Identification ) ),
Col Mean( :MG1, :Identification ) - Col Mean( :MG2, :Identification ),
.
)
