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?
"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.