topic Re: Calculating Day 1 Baseline in Discussions

Calculating Day 1 Baseline

DNicholls — Wed, 17 Jan 2018 22:16:51 GMT

I have a Result for Day 1 for some subjects. For each subject, I would like to use this as a baseline and subtract it from subsequent days.

I can calculate the baseline if I sort the table on Day (see the screenshot), but this is not a solution, as I need to be able to sort the table on other fields.

How can I calculate the Day 1 Baseline result for each subject and still be able to sort as I need?

Thanks

Dave

BTW (ignore the subjects with no Day 1)

Re: Calculating Day 1 Baseline

txnelson — Wed, 17 Jan 2018 22:18:24 GMT

1. Sort the data as required to calculate the baseline

2. Generate the new column using the formula.

3. Remove the formula column property, which will convert the cell values in the formula column to static values.

4. Now sort the data as needed, and the baseline values will remain fixed.

Re: Calculating Day 1 Baseline

DNicholls — Wed, 17 Jan 2018 22:28:09 GMT

Thanks for the quick reply. This does work and is simple.

I may have to create some sort of template where the user doesn't have to do anything other than open a data file.

Is there a formula or script that I can use to calculate the baseline without the user having to do anything?

Thanks

Dave

Re: Calculating Day 1 Baseline

txnelson — Wed, 17 Jan 2018 23:57:33 GMT

If I new more about how the users get the data etc. I might be able to suggest how to go about this. Typically I would suggest an addin that would guide the userts through loading the data and making any modifications to the data before the analysis. But, without knowing their process I would be just guessing in the dark.

Re: Calculating Day 1 Baseline

Oren_Bar-Ilan — Fri, 02 Nov 2018 05:43:02 GMT

Hello,

Here is another simple way of doing this without using scripts:

1) Select only baseline rows, for example by using the distribution of days and selecting baseline rows on chart.

2) Use Tables to create a subset table that includes only those rows. You may want to change the name of the column with baseline results.

3) Join the original table with the subset table so a new column with baseline results only wll be added to the original table with repeated baseline values for each subject.

4) Create a new column with a formula of change from baseline. This way different formulas can be easily tested (difference, ratio or %change from BL) and similarly applied to multiple parameters within the same table.

Takes a minute to do and is very flexible.