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

rperkins — Fri, 09 Jun 2017 22:25:37 GMT

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/5477/highlight/true

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.

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

Craige_Hales — Sat, 10 Jun 2017 01:48:38 GMT

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

formula column using ColMean

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

rperkins — Mon, 12 Jun 2017 15:51:40 GMT

Thanks Craig. This worked.

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

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

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

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