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
szfunh
Level I

Missing value codes and formulas

When I set a missing value code in a continuous data column, and then write a formula in another column that refers to the first column, the formula includes the missing value code (-9999) in the calculation. For a simple example, I have a column of temperatures in Kelvin that I am converting in the second column to Celsius. Wherever I have a missing value code in the Kelvin column, the calculated value in the new Celsius column is -10,272.15. I would like the second column to also show the missing value code. I would just delete all of the -9999s, but we want to be able to distinguish between data that is missing because we did not collect it, and data that is missing due to instrument malfunctions. What am I doing wrong?

1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: Missing value codes and formulas

What version of JMP are you using?

We've changed this behavior with JMP 12. Functions in the formula editor now respect the Missing Value Code property. Earlier versions (as you've discovered) didn't do this.

If you want to account for this in your conversion in JMP 11 and earlier you'll need to put a conditional in your formula.

9208_JMPScreenSnapz002.png


If(:Temp Kelvin == -9999, -9999, :Temp Kelvin - 272.15)


-Jeff

-Jeff

View solution in original post

6 REPLIES 6
Jeff_Perkinson
Community Manager Community Manager

Re: Missing value codes and formulas

What version of JMP are you using?

We've changed this behavior with JMP 12. Functions in the formula editor now respect the Missing Value Code property. Earlier versions (as you've discovered) didn't do this.

If you want to account for this in your conversion in JMP 11 and earlier you'll need to put a conditional in your formula.

9208_JMPScreenSnapz002.png


If(:Temp Kelvin == -9999, -9999, :Temp Kelvin - 272.15)


-Jeff

-Jeff
szfunh
Level I

Re: Missing value codes and formulas

Thanks For your answer, Jeff. I am using JMP 11, but I guess my example was too simple to elucidate the real problem. I'm not sure how to write a conditional statement (or statements) that will deal with -9999s when I have 3 columns that I am trying to average. For example, I have 3 columns of soil temperature data, and sometimes one, two, or three of the columns will have -9999s. This programming is beyond my level of understanding. Maybe I should just get JMP 12... Any insight would be appreciated!

pmroz
Super User

Re: Missing value codes and formulas

No need for JMP 12.  Here's an example IF statement that averages three columns if none of them are -9999:

if (:Temp_Kelvin  != -9999 &

    :Temp2_Kelvin != -9999 &

    :Temp3_Kelvin != -9999,

    (:Temp_Kelvin + :Temp2_Kelvin + :Temp3_Kelvin) / 3;

);

szfunh
Level I

Re: Missing value codes and formulas

Thanks PMRoz, but I'm wondering if I can take the average of the two columns that don't have -9999 (if there is one column missing), or just use the value from the one column that has data (if both other values are missing). For example:

Soil temp 1, Soil temp 2, Soil temp 3

287.1, 289.3, 288.6

-9999, 286.6, 287.9

-9999, 287.5, -9999

-9999, -9999, -9999

I want to take the average of all three values in the first line, cols 2 and 3 in the second line, use the value from col 2 in the third line, and return a -9999 for the fourth. Is this possible with conditional statements?

pmroz
Super User

Re: Missing value codes and formulas

This could do it. 

(match(:Temp_Kelvin, -9999, 0, :Temp_Kelvin) +

match(:Temp2_Kelvin, -9999, 0, :Temp2_Kelvin) +

match(:Temp3_Kelvin, -9999, 0, :Temp3_Kelvin) )

/

(match(:Temp_Kelvin, -9999, 0, 1) +

match(:Temp2_Kelvin, -9999, 0, 1) +

match(:Temp3_Kelvin, -9999, 0, 1) )

pmroz
Super User

Re: Missing value codes and formulas

The MATCH function does the trick.  From the Scripting guide:

Match

You can use the Match() function to make several equality comparisons without needing to rewrite the value to be compared. The syntax is:

    Match(x, value1, result1, value2, result2, ..., resultElse)

For example, the following script recodes gender abbreviations in Big Class.jmp:

For Each Row(

     sex = Match( sex, "F", "Female", "M", "Male", "Unknown" )

);