281289_en-US

liliconcom

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!

Advanced Statistical ModelingDesign of ExperimentsRe: Custom formula to normalize a data set by sub-set averages

I published an Addin : Normalization GUI  which facilitates such normalizations.

 

 

Re: Custom formula to normalize a data set by sub-set averagescorrectRe: 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.

:thumbs_up::thumbs_up:

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 )