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

[#1]

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

[#2]

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?

[#3]

Hi Jarmo,

I have provided the Table in excel and in JMP.

THank you,

Tim

[#4 · Accepted solution]

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
)