topic Col formula for iterative means by categories in Discussions
https://community.jmp.com/t5/Discussions/Col-formula-for-iterative-means-by-categories/m-p/212726#M42586
<P>Hi,</P><P> </P><P>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.</P><P> </P><P>To calculate the carry-over level I first do 10 baseline measurements on each device-reagent combination.</P><P>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.</P><P> </P><P>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.</P><P> </P><P>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.</P>Wed, 12 Jun 2019 09:27:52 GMTolekh2019-06-12T09:27:52ZCol formula for iterative means by categories
https://community.jmp.com/t5/Discussions/Col-formula-for-iterative-means-by-categories/m-p/212726#M42586
<P>Hi,</P><P> </P><P>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.</P><P> </P><P>To calculate the carry-over level I first do 10 baseline measurements on each device-reagent combination.</P><P>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.</P><P> </P><P>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.</P><P> </P><P>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.</P>Wed, 12 Jun 2019 09:27:52 GMThttps://community.jmp.com/t5/Discussions/Col-formula-for-iterative-means-by-categories/m-p/212726#M42586olekh2019-06-12T09:27:52ZRe: Col formula for iterative means by categories
https://community.jmp.com/t5/Discussions/Col-formula-for-iterative-means-by-categories/m-p/212758#M42590
<P>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.</P>
<P>Here are the formulas I used</P>
<P>Mean(Baseline)</P>
<PRE><CODE class=" language-jsl">x = Col Mean( If( :Sample == "Baseline", :Measurement, . ), :Reagent, :Device );
If( :Sample == "Baseline",
x = .
);
x;</CODE></PRE>
<P>H Sample</P>
<PRE><CODE class=" language-jsl">x = Col Max( :Measurement, :Reagent, :Device );
If( :Sample == "Baseline",
x = .
);
x;</CODE></PRE>Wed, 12 Jun 2019 14:40:48 GMThttps://community.jmp.com/t5/Discussions/Col-formula-for-iterative-means-by-categories/m-p/212758#M42590txnelson2019-06-12T14:40:48ZRe: Col formula for iterative means by categories
https://community.jmp.com/t5/Discussions/Col-formula-for-iterative-means-by-categories/m-p/212769#M42592
<P>Hi <LI-USER uid="14123"></LI-USER>,</P>
<P>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:</P>
<P> </P>
<PRE><CODE class=" language-jsl">If( :Sample == "Baseline",
Col Mean( :Measurement, :Reagent, :Sample, :Device ),
Lag( :Name( "Mean(Baseline) Formula" ), 1 )
)</CODE></PRE>
<P>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. </P>
<P> </P>
<P><LI-USER uid="2026"></LI-USER> </P>Wed, 12 Jun 2019 14:54:32 GMThttps://community.jmp.com/t5/Discussions/Col-formula-for-iterative-means-by-categories/m-p/212769#M42592julian2019-06-12T14:54:32ZRe: Col formula for iterative means by categories
https://community.jmp.com/t5/Discussions/Col-formula-for-iterative-means-by-categories/m-p/212916#M42607
Thank you, that is perfect! And you are right about the means, of course.Thu, 13 Jun 2019 08:02:20 GMThttps://community.jmp.com/t5/Discussions/Col-formula-for-iterative-means-by-categories/m-p/212916#M42607olekh2019-06-13T08:02:20ZRe: Col formula for iterative means by categories
https://community.jmp.com/t5/Discussions/Col-formula-for-iterative-means-by-categories/m-p/212917#M42608
Yes, you are right about the means. It seems I was a bit hasty transferring values to the example set. Thanks for your solution!Thu, 13 Jun 2019 08:04:34 GMThttps://community.jmp.com/t5/Discussions/Col-formula-for-iterative-means-by-categories/m-p/212917#M42608olekh2019-06-13T08:04:34Z