Subscribe Bookmark RSS Feed

missing data in calculated formula

schniggeldorf

Community Trekker

Joined:

Jun 23, 2011

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.
2 REPLIES
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...
schniggeldorf

Community Trekker

Joined:

Jun 23, 2011

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