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
TYP
TYP
Level I

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
txnelson
Super User

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

View solution in original post

3 REPLIES 3
Highlighted
txnelson
Super User

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

View solution in original post

Highlighted
TYP
TYP
Level I

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
txnelson
Super User

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

correct
Jim