Background subtraction based on variables

Subbu

Hi Everyone,

 

I am new to JMP and I need some help on selecting the specific rows from a column and subtract rest of the column based on the variable. Herewith, I have attached a data in which you can see  Target, Replicates, Cell, Data order and Data. Basically, I wanted to subtract the background data from rest of the data with cell and data order specific. For instance, let's consider D in Target column is the background, I wanted the JMP to calculate the mean of D in every Cell and Data Order and subtract the rest of the data from the respective Cell and Data Order automatically.

 

Please let me know if you need more information! Thank you for helping me

Data.jmp

ms

For cases like this I often use Summary followed by Update (Tables menu).

Make a Summary table with mean of Data (grouped by Target, Cell & Data Order), delete rows except for Target = D, and updade the original with the summary table (match by columns Cell and Data Order).

 

If you need a dynamic solution it is possible with a Column Formula, however they can get quite complex. This formula should work for your example table:

:Data - If(:Target == "D",
    Col Mean(:Data, :DataOrder, :Target == "D"),
    (Col Sum(:Data, :Cell, :DataOrder) - Col Sum(:Data, :Cell, :DataOrder, :Target == "D")) / (
    Col Number(:Data, :Cell, :DataOrder) - Col Number(:Data, :Cell, :DataOrder, :Target == "D"))
);