topic Re: Normalize a column in Discussions

Normalize a column

dnh234589 — Mon, 12 Jan 2026 21:15:31 GMT

TLDR: How do I normalize a column to itself?

See attached. I have:

Column A = 0,1,2,3. Maybe think of this as like an ID number
Column B. Think of this as a timestamp
Column C. This is the data

Now I want column D to have the data in Column C, ONLY when B=1.

As you can see in the attached file, I have manually copy-pasted the yellow cells from C into D. Pasted over and over.

Therefore when I do D/C, I'm basically normalizing the whole of column C, to these special yellow values. This is the reason I'm doing all this, is to find D/C. So a value of 0.9 in this column means C is lower than the yellow C's, and 1.1 means it's higher than the yellow C's.

So, long story short, what is the formula that goes in D, to be able to get the yellow values?

Re: Normalize a column

mmarchandFSLR — Mon, 12 Jan 2026 21:33:56 GMT

edit

Sorry, I need to pay more attention. You weren't asking how to get D/C.

~~Select C and D, then right click. New Formula Column > Combine > Ratio (reverse order)~~

Re: Normalize a column

mmarchandFSLR — Mon, 12 Jan 2026 21:54:25 GMT

Sorry if I'm still misunderstanding, but if you want D to be missing when B is not 1 and D to have the values of C when B is one, try this formula.

Choose( :B, :C, . )

Re: Normalize a column

dnh234589 — Tue, 13 Jan 2026 00:31:27 GMT

Thanks for your answer. Not exactly. I simply want a formula in column D, that will reproduce those numbers.

That formula will take the yellow cells in C, and apply it to the entire column D. So that now the entire column D is the repeated yellow cells as shown. (Presumably in the formula somewhere, we would hardcode B=1.)

Probably I confused things by manually typing in D in the example. I manually typed in those numbers, but I want a formula that will populate D for me.

Re: Normalize a column

dnh234589 — Tue, 13 Jan 2026 00:33:44 GMT

Thanks for your answer, but here is a comparison of your answer, and what I want which is just a formula for D. So compare the final column and D and you'll see they are not the same.

Re: Normalize a column

txnelson — Tue, 13 Jan 2026 03:24:30 GMT

Isn'1 the formula just

If( :B == 1, 1, :D / :C )

Re: Normalize a column

christian-z — Tue, 13 Jan 2026 10:52:34 GMT

I'm sure there is a way to script a solution, but I would instead propose that switching from your Tall data format to a Wide data format makes more sense in this case. When using JMP formulas, it is always easiest when all the terms needed for that calculation are in the same row. (Sometimes this is impossible, then clever scripting is needed. But in this case, it is possible.)

My suggestion: Split the data table so that it is in a Wide format, with a separate Column for each "B". Then normalizing (diving by C) becomes easy.

You can recode the column names to make them a bit more readable:

After adding the new formula columns, you end up with:

Would this work for you? I attached the table for your reference.

Re: Normalize a column

hogi — Tue, 13 Jan 2026 11:45:33 GMT

Use a grouped Col aggregation like col Median() to define the values of column D as

Col median(if (:B==1,:C), :A)

You can also skip Column D and directly calculate :C/Denominator.
There is an AddIn in the Marketplace which does such Normalization automatically.

Re: Normalize a column

dnh234589 — Tue, 13 Jan 2026 15:18:05 GMT

No, I want to find the formula for D. In my example I just manually typed in the numbers for D but I want a formula

Re: Normalize a column

hogi — Tue, 13 Jan 2026 15:24:26 GMT

what is wrong with

Col median(if (:B==1,:C), :A)

Re: Normalize a column

dnh234589 — Tue, 13 Jan 2026 20:04:02 GMT

Hogi, This is the solution! Thanks! A little weird that I have to use a "median" function since there is no median in sight, but I see what it's doing. Thanks! I'll also check out the Marketplace Addin

Re: Normalize a column

dnh234589 — Tue, 13 Jan 2026 20:05:18 GMT

Thanks! This does work, but is a bit unweildy for a human to view when I have a large dataset. Hogi's answer is simpler but thanks for the tip anyway!