topic Re: If statement Formula not working in Discussions

If statement Formula not working

Datamom1 — Wed, 06 Feb 2019 20:36:47 GMT

For the past couple years when I copy and paste the formula below it has not worked consistently. I've tried typing it in the formula box and have at times been succesful but it's not consistent.

I was finally upgraded to JMP 14 last week and an option that is awesome is that I can preview a data sample to see what the formula result will look like. However, I can't figure out why this formula won't work. Below is a screenshot of the formula as well as the preview of the dataset and formula result.

Are there any suggestions?

Re: If statement Formula not working

txnelson — Wed, 06 Feb 2019 20:49:51 GMT

A possible issue may be that JMP may be having an issue with

Behaviorial - physical aggression to staff

Is this a calculation of a column or variable called

Behavioral

from which is subtracted a column or variable called

physical aggression to staff

is this a single column or variable called

Behavioral - physical aggression to staff

JMP can get confused over this. If it is one column and not a calculation, you should actually state the formula as

If( :Name("Behavioral - physical aggression to staff") == 1,
     1,
     0
);

The JMP formula editor will not display the :Name() function, but by you putting into your formula, it will make sure JMP understands what you are specifying.

Re: If statement Formula not working

Craige_Hales — Wed, 06 Feb 2019 21:44:22 GMT

Also, if the 1s in the table were calculated, they might not be exactly 1 (which is what you are testing for). The table displays a value that is rounded to about 10 digits. Try

Round(:Name("Behavioral - physical aggression to staff"), 5) == 1

and see if that works better.

Re: If statement Formula not working

Craige_Hales — Wed, 06 Feb 2019 21:47:27 GMT

@ErnestPasour for the awesome comment!

Re: If statement Formula not working

Wendy_Murphrey — Wed, 06 Feb 2019 22:16:53 GMT

The data looks to be left justified in the preview. Is the data type set to character for the Behavioral - physical aggression to staff column? If so, you will need to add double-quotes around the number on the right side of the == operator or change the data type of the column.

Re: If statement Formula not working

Datamom1 — Thu, 07 Feb 2019 14:52:53 GMT

Thank you very much! That worked perfectly! This will save me a lot of time.