cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
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.