Background subtraction based on variables

[#1]

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 :)

[#2]

Hi, Subbu!

More information will be helpful!

For instance, do you need 25 (mean of Target D in Cell/DataOrder 1) subtracted from all the Cell/DataOrder 1's, and 22 (mean of Target D in Cell/DataOrder 2) subtracted from all the Cell/DataOrder 2's?  Or something else?

Are Cell and DataOrder always identical?

Is there any specific reason why there are 7680 rows (with 7664 missing) in this datatable?

[#3]

Hi Kevin,

Thank you for the reply! Yes, you are right in the subtraction part! However, the Cell and Data order order are not identical. The Cell ranges from 1-4 and Data Order ranges from 0-14 or more. I apologize for the file! I did delete those information and put some random numbers for demonstration!

Thank you

Subbu

[#4 · Accepted solution]

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"))
);

[#5]

Hi MS,

Thank you for the help! The column formula works great for the most part. However, in a large dataset I see the mean of Target D doesn't subtract the Target D. Any idea?

Thank you

Subbu