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

TYP

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!

txnelson

Your formula is oh sooooooooo close.   

Try

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