cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
RanhoAcido
Level I

Complex formula for transformation of data in fold change and log (2) change from Baseline

Well complex is a relative term but the way I receive data is in a bundle that has multiple analytes and multiple IDs. We always have per Analyte and Patient ID a singular baseline Timepoint ("C1D1 0"). I can do it like excel but honestly a much more elegant way of doing is to make a formula for when I receive another batch of data of longitudinal data I can just add that and it will be transformed. Also, having such formulas would help for another analysis that will also be delivered in the same fashion.

 

So, basically I want to create a formula that matches (or groups?) an Analyte and Patient ID, then finds the Timepoint "C1D1 0" and uses the "Result" cell as baseline and the simplest transformation would be fold change Result X/Result Baseline. All other transformations would be derivatives, %change from baseline and log2 change from baseline.

 

Please let me know if I have explained this properly but in essence I would like a formula to do the heavy lifting without the need for too much data manipulation.

 

Many thanks for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

Re: Complex formula for transformation of data in fold change and log (2) change from Baseline

So you basically just need formula to get the baseline for each Analyte + Patient ID pair? This is one suggestion for that

:Result[Col Min(If(:Timepoint == "C1D1 0", Row(), .), :Patient ID, :Analyte)]

After you have the baseline you can fairly easily create the other formulas using that.

-Jarmo

View solution in original post

2 REPLIES 2
jthi
Super User

Re: Complex formula for transformation of data in fold change and log (2) change from Baseline

So you basically just need formula to get the baseline for each Analyte + Patient ID pair? This is one suggestion for that

:Result[Col Min(If(:Timepoint == "C1D1 0", Row(), .), :Patient ID, :Analyte)]

After you have the baseline you can fairly easily create the other formulas using that.

-Jarmo
RanhoAcido
Level I

Re: Complex formula for transformation of data in fold change and log (2) change from Baseline

Many thanks for that! I can definitely now transform the result anyway I want

 

Much appreciated!