topic Calculating percent of initial concentration on experiments with different Reagents on different Days in Discussions

Calculating percent of initial concentration on experiments with different Reagents on different Days

timothy_forsyth — Fri, 21 Feb 2025 04:31:38 GMT

Hi,

I am using JMP 16. I have the following Table

The first column has reagent concentrations at time T =0, T = 2 h, T = 6 h, T = 24 h, and T = 48 h. These experiments are done on different dates with different brands of reagents. For every unique date and reagent brand I would like calculate the percentage of reagent left at after T = 2 h, T = 6 h,, T = 24 h, and T = 48 h. I could do it manually by dividing the T = N h (N = 2, 6, 24, and 48) by the corresponding T = 0 for that date and reagent brand. Is there a way to automate it using an index column and a Table split?

Thank you,

Tim Forsyth

Re: Calculating percent of initial concentration on experiments with different Reagents on different Days

jthi — Fri, 21 Feb 2025 05:00:06 GMT

This can be usually done with a formula, sometimes simple one sometimes a bit more complicated, usually depending on possible grouping columns (looks like Date + reagent could be used in this case). Can you provide your table to make it easier to provide an example with at least few correct answers added?

Re: Calculating percent of initial concentration on experiments with different Reagents on different Days

timothy_forsyth — Fri, 21 Feb 2025 05:14:25 GMT

Hi Jarmo,

I have provided the Table in excel and in JMP.

THank you,

Tim

Re: Calculating percent of initial concentration on experiments with different Reagents on different Days

jthi — Fri, 21 Feb 2025 07:19:33 GMT

This is one possible formula you can use. This does assume that your data is sorted and utilizes Col Cumulative Sum to access the first value of Date + reagent groups

Edit: added grouping to col cumulative sum

:"Avg. Concentration (uM)"n / Col Cumulative Sum(
	If(Row() == Col Min(Row(), :Date, :Type of reagent),
		:"Avg. Concentration (uM)"n,
		.
	),
	:Date, :Type of reagent
)

It can also be modified to look for the row with Time (h) == 0 but it didn't seem to be necessary in this case as it is the first value for each group.

Edit: This should take the value from each group when Time (h) is 0

:"Avg. Concentration (uM)"n / Col Min(
	If(:"Time (h)"n == 0,
		:"Avg. Concentration (uM)"n,
		.
	),
	:Date,
	:Type of reagent
)