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

timothy_forsyth

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

jthi

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
)