Automatic Calculation of % Yield Change

[#1]

Hello,

I'm working with JMP 17.0.0 - I'm trying to find a way to calculate the % Change between two conditions in a column that will update automatically as new data is added.  See the attached data file for reference.

Presently to find this, I make a Tabulate table where I will use the Trial Phase column in the column zone and drop the calculated yield per batch (Ethanol/Liq Solids) into the drop zone for rows.  Then using the average yield for Baseline from this table, I then make a new column where I calculate:

[:"Ethanol/Liq Solids" - ("Baseline" average ethanol/liq solids from Tabulate)] / ("Baseline" average ethanol/liq solids from Tabulate again)

I change the column properties to percent with two decimals, and this gives me exactly what I need (as shown in the data table).  But the trouble is that if I add more baseline data, then I need to pull up the Tabulate again and find the new baseline average, then update the formula column to correct it.  Is there a way to set JMP to update this baseline average automatically so I don't need to update it every time?  The automation would also be useful in the situation where I might need to exclude some "baseline" data for one reason or another and have the % change update with that as well.

As this particular trial progresses, more trial phases will be added and I would need all of them to reference back to the average of the "Baseline" yield to calculate the amount of yield gain (or loss) relative to the Baseline average.  I'm fairly proficient with the program and can follow along with JSL commands, but it would be ideal if the solution was easy enough to pass along to colleagues that will face the same situation but might not be able to follow along quite as easily.

Thanks in advance!

[#2]

I believe that all you need to do is to calculate the average of Ethanol/Liq Solids

(:"Ethanol/Liq Solids"n - Col Mean( :"Ethanol/Liq Solids"n )) /
Col Mean( :"Ethanol/Liq Solids"n )

[#3]

Hi Jim, unfortunately that would average the Baseline along with the POC condition (which is only in one row - the very last one).  I need to segregate the average of just the baseline from the POC and other future trial phases.

[#4 · Accepted solution]

You can use the formula @txnelson provided as the starting point and add :Trial Phase as ByVar

(:"Ethanol/Liq Solids"n - Col Mean(:"Ethanol/Liq Solids"n, :Trial Phase)) /
Col Mean(:"Ethanol/Liq Solids"n, :Trial Phase)

https://www.jmp.com/support/help/en/17.2/#page/jmp/statistical-functions-2.shtml?os=win&source=appli...

If you need to use same Baseline mean also for POC, you have to make small modifications: one option is to use If inside your Col Mean which will set value to Missing when you aren't on "Baseline" row

(:"Ethanol/Liq Solids"n - Col Mean(If(:Trial Phase == "Baseline", :"Ethanol/Liq Solids"n, .))) /
Col Mean(If(:Trial Phase == "Baseline", :"Ethanol/Liq Solids"n, .))

[#5]

Thanks Jarmo, that second equation worked perfectly.  I tried so many If functions that didn't pan out, I can't believe I didn't think to use it in that application.

[#6]

Do you know of anything I can add to the formula that would recalculate in the event that some Baseline rows are excluded?

[#7 · Accepted solution]

You can add Excluded() as byvar and excluded rows will have their own values calculated but won't affect the non-excluded rows

(:"Ethanol/Liq Solids"n - Col Mean(If(:Trial Phase == "Baseline", :"Ethanol/Liq Solids"n, .), Excluded()))
 / Col Mean(If(:Trial Phase == "Baseline", :"Ethanol/Liq Solids"n, .), Excluded())

[#8]

Awesome.  Thank you so much.