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

[#1]

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.

[#2 · Accepted solution]

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.

[#3]

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

Much appreciated!