turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Missing value codes and formulas

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 10, 2015 9:55 AM
(1265 views)

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Solution

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.

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

-Jeff

-Jeff

6 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

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

-Jeff

-Jeff

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 21, 2015 9:08 AM
(996 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 21, 2015 10:07 AM
(996 views)

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**;

**)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 21, 2015 10:18 AM
(996 views)

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 21, 2015 11:20 AM
(996 views)

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)** **)**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jul 21, 2015 11:29 AM
(996 views)

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" **)**

**)**;