Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 12, 2019 2:27 AM
(2553 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Col formula for iterative means by categories

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

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.