Col formula for iterative means by categories

[#1]

Hi,

I have a dataset containing measurements from an experiment measuring the analyte carry-over from a high concentrated sample to the subsequent low concentration samples on a chemical analyzer. I do the experiment on different devices and different reagents, doing three replicate runs for each combination.

To calculate the carry-over level I first do 10 baseline measurements on each device-reagent combination.

I want to make a new column containing the mean of the 10 baseline measurements for the corresponding device-reagent combination. Attached is the data table with the wanted column filled in manually in the "Mean(baseline)" col.

I also want a new column that takes the measuments result of the high samples ("H" in col "Sample") and fills in this value for the ten following low samples. Again, see attached.

I have some idea of how to hard code the col formulas to device names and sample names, but I would like to do it iteratively, as the real dataset is larger.

[#2 · Accepted solution]

I have attached a data table with two new columns that calculate the Mean(Baseline) and the H Sample.  I added them to your attached data table as two new columns.  Unless you did not specify your requirements properly, I found that your manually calculated Mean(Baseline) values were not correct for the Reagent/Device Baseline means.  They appear to be the mean for the Baseline values for the previous Reagent/Device grouping.

Here are the formulas I used

Mean(Baseline)

x = Col Mean( If( :Sample == "Baseline", :Measurement, . ), :Reagent, :Device );
If( :Sample == "Baseline",
	x = .
);
x;

H Sample

x = Col Max( :Measurement, :Reagent, :Device );
If( :Sample == "Baseline",
	x = .
);
x;

[#3]

Thank you, that is perfect! And you are right about the means, of course.

[#4]

Hi @olekh,

If your data is sorted such that the baseline measurements always come before the other runs (within a sample/device/reagent group), which is what you have here, the following is one such column formulas that will do this:

If( :Sample == "Baseline",
	Col Mean( :Measurement, :Reagent, :Sample, :Device ),
	Lag( :Name( "Mean(Baseline) Formula" ), 1 )
)

I've attached the table with the formula here, too. But, I was a bit confused that the means I got did not match your hardcoded means. I checked manually by calculating the baseline means separately and they didn't match what you had. Is it possible the means you calculated were incorrect? Or perhaps, I'm misunderstanding your data structure. 

@julian 

[#5]

Yes, you are right about the means. It seems I was a bit hasty transferring values to the example set. Thanks for your solution!