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.
Index Value Baseline Normalized value
1 2 Yes 2/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 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!
Go to Solution
Your formula is oh sooooooooo close.
:Value / Col Mean( If( :Baseline == "Yes", :Value, . ), :Index )
View solution in original post
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.