Subscribe Bookmark RSS Feed

script average of three columns

mallen810

Contributor

Joined:

Dec 9, 2016

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.

 

Screen Shot 2016-12-14 at 2.19.05 PM.png

2 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

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

Jim
mallen810

Contributor

Joined:

Dec 9, 2016

Thanks, by recoding the data it made it much simpler.