cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
rperkins
Level I

How do I use an if statement and a by variable correctly in a column mean formula?

I'm trying to use the columne mean formula to show the Time 0 average value for a columen of test samples, grouped by the test sample types. 

 

I found this topic from 2012, that looked like it would work:

https://community.jmp.com/t5/Discussions/Determine-column-mean-by-day-and-within-certain-hours/m-p/5...

 

The recommended syntax there is:

Col Mean( If( 1 <= :Hour <= 10, :x, Empty() ), :Day )

 

However, when I try to use something similar with my data set, I get an "argument should be numeric 1 times at rows: 2" error. 

 

This is the syntax I'm trying to use.

Col Mean(If( :DH hours == 0, :pm_mw, Empty() ), :Substrate)

 

I can get the forumla to execute correctly if I eliminate the by variable: 

Col Mean(If( :DH hours == 0, :pm_mw, Empty() ))

 

Similarly, I can get it to evaluate correctly if I eliminate the I statement:

Col Mean(:pm_mw, :Substrate)

 

And finally, I can get it to evaluate correctly if all the values for DH hours in the table are 0. But if there are any non-0 values for DH hours in the table, it won't evalute. 

 

Any suggestions would be appreciated. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Craige_Hales
Super User

Re: How do I use an if statement and a by variable correctly in a column mean formula?

Instead of empty(), use a missing value, represented by period.

formula column using ColMeanformula column using ColMean

Craige

View solution in original post

2 REPLIES 2
Craige_Hales
Super User

Re: How do I use an if statement and a by variable correctly in a column mean formula?

Instead of empty(), use a missing value, represented by period.

formula column using ColMeanformula column using ColMean

Craige
rperkins
Level I

Re: How do I use an if statement and a by variable correctly in a column mean formula?

Thanks Craig. This worked.