recherche d'une formule avec une condition

Françoise

bonjour,

 

je cherche à créer une colonne qui calculerait la différence MG1 - MG2 si la valeur dans la colonne Identification est identique.

 

donc la différence entre les 2 valeurs jaunes ou entre les 2 valeurs en bleu.

quelle serait la formule ?

 

cordialement

GregF_JMP

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 ),
	.
)