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
- :
- Re: missing data in calculated formula

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
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

missing data in calculated formula

Jul 16, 2010 11:22 AM
(1833 views)

I am writing a script that asks JMP to create a new column in my data table, then populate that column with the sum of the values of 25 existing columns. It works fine, except that if there is missing data in any of the 25 columns, the total displays as 0 (zero), when it should display as missing. It won't work to tell JMP that 0 is not a valid value, because in some cases each of the 25 columns has a value of 0, and thus it would be a valid total.

1. How can I instruct JMP to set the value of a formula to "missing" if one of it's components is missing?

2. Is there a way in JMP to tell it, for example, to replace up to 2 missing values with the average of the remaining 23, but to count the case as missing if >2 values are absent?

Thanks.

1. How can I instruct JMP to set the value of a formula to "missing" if one of it's components is missing?

2. Is there a way in JMP to tell it, for example, to replace up to 2 missing values with the average of the remaining 23, but to count the case as missing if >2 values are absent?

Thanks.

2 REPLIES 2

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

Re: missing data in calculated formula

As for the 1st question, I think I do get missing values instead of zero...

So I opened big class and set "height" for the 1st row to be missing and created another column calculating "height+weight". Then the first row of the new column shows "." instead of 0...

So I opened big class and set "height" for the 1st row to be missing and created another column calculating "height+weight". Then the first row of the new column shows "." instead of 0...

Highlighted
##

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

Re: missing data in calculated formula

Thanks. I found the answer elsewhere:

"There are three ways to sum across columns in JMP, and each behaves differently. Suppose your two columns are Col1 and Col2 and for a given row, Col2 is missing.

--Sum(Col1 + Col2) returns a zero.

--Sum(Col1, Col2) returns the value of Col1. It treats Col2 as if it is a zero

--Col1 + Col2 returns a missing value. The logic is that a number plus a missing value is missing. "

I'd been using the second method, and getting zeros when both col1 and col 2 were zero.

Thanks for your help.

ES

"There are three ways to sum across columns in JMP, and each behaves differently. Suppose your two columns are Col1 and Col2 and for a given row, Col2 is missing.

--Sum(Col1 + Col2) returns a zero.

--Sum(Col1, Col2) returns the value of Col1. It treats Col2 as if it is a zero

--Col1 + Col2 returns a missing value. The logic is that a number plus a missing value is missing. "

I'd been using the second method, and getting zeros when both col1 and col 2 were zero.

Thanks for your help.

ES