I am trying to make an average column once data has been pulled off of our server. The data is in three seperate columns, but some of the columns may only have one value and the other two have a 0. When setting up the formula if there are zero's it still does an average of the three, thus giving me a incorrect value. I would like to do this in a script if possible.
Here is a formula that will work
Mean( If( :Name( "Average-PURITY_1" ) != 0, :Name( "Average-PURITY_1" ), . ), If( :Name( "Average-PURITY_2" ) != 0, :Name( "Average-PURITY_2" ), . ), If( :Name( "Average-PURITY_3" ) != 0, :Name( "Average-PURITY_3" ), . ), )
However, what should really be done is to change the 0's to missing values ".". Zeros are valid values. I suggest that you use the recode capability in the Cols pulldown menu to recode your 0's to missing values, and then the Mean function can be used without a need to have the IF functions