BookmarkSubscribe
Choose Language Hide Translation Bar
Community Trekker

missing data in calculated formula

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 2

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...
Highlighted
Community Trekker

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