Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted

## Custom formula to normalize a data set by sub-set averages

Hi,

I am looking to normalize a set of data with the Mean calculated with the following restrictions:

- Mean(Value) by Index

- Only those rows with Baseline=="Yes" are included in the mean calculations

- All rows with the same Index will be normalized by the Mean calculated above.

For example,

Index   Value   Baseline     Normalized value

1           2         Yes             2/Mean(2,3,3)

1           3         Yes             3/Mean(2,3,3)

1           3         Yes             3/Mean(2,3,3)

1           2         No              2/Mean(2,3,3)

2           4         Yes             4/Mean(4,4)

2           4         Yes             4/Mean(4,4)

2           3         No              3/Mean(4,4)

2           4         No              4/Mean(4,4)

I tried the following but it does not put the correct Mean in the rows where Baseline != "Y"

Value/Col Mean( :Response, :Index, :Baseline  == "Y" )

Thanks in advance for any suggestions!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

## Re: Custom formula to normalize a data set by sub-set averages

Your formula is oh sooooooooo close.

Try

``:Value / Col Mean( If( :Baseline == "Yes", :Value, . ), :Index )``
Jim
3 REPLIES 3
Highlighted

## Re: Custom formula to normalize a data set by sub-set averages

Your formula is oh sooooooooo close.

Try

``:Value / Col Mean( If( :Baseline == "Yes", :Value, . ), :Index )``
Jim
Highlighted

## Re: Custom formula to normalize a data set by sub-set averages

Thanks a lot @txnelson  for the solution! It works exactly how I needed it to.

Let me see if I understand the formula:

- If :Baseline is "Yes", then :Value is sent for Col Mean calculation, to be grouped by Index

- Else no value (.) is sent for Col Mean calculation.

Highlighted

correct
Jim
Article Labels