World Statistics Day was yesterday, but we’re celebrating all week long! This celebration means acknowledging the impact statistics has on our world. Who is your favorite statistician? Share with us who they are and why they top your favorites list.
Choose Language Hide Translation Bar
Highlighted
Level II

## Col formula for iterative means by categories

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: Col formula for iterative means by categories

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;``````
Jim
4 REPLIES 4
Highlighted
Super User

## Re: Col formula for iterative means by categories

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;``````
Jim
Highlighted
Level II

## Re: Col formula for iterative means by categories

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

## Re: Col formula for iterative means by categories

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.

Level II

## Re: Col formula for iterative means by categories

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

There are no labels assigned to this post.