I am trying to add 3 columns of continous data and put the resulting sum in a fourth column. if any of the three columns have missing data, no sum is placed in the fourth column.
Assuming you're using a column formula, try this. The example assumes your columns are named C1, C2, C3 and C4. This is the column formula for column C4:
If( Is Missing( :C1 ), 0, :C1) +
If( Is Missing( :C2 ), 0, :C2) +
If( Is Missing( :C3 ), 0, :C3)
Another option is to use the Sum() function instead of the addition operator.
The Sum() function doesn't propagate missing values, while the + operator does.
-Jeff
Do I find the If in the “Conditional” part of the formula box? When I use
that If, I also have a else statement to deal with.
Thanks,
Desta
From: PMroz
Sent: Wednesday, August 20, 2014 11:49 AM
To: Desta Ledom
Subject: Re: - I am trying to add 3 columns of continous
data and put the resulting sum in a seperate column. if any of the three
columns have missing data, no sum is placed in the fourth column.
JMP User Community <https://community.jmp.com/>I am trying to add 3 columns
of continous data and put the resulting sum in a seperate column. if any
of the three columns have missing data, no sum is placed in the fourth
column.
reply from PMroz
<https://community.jmp.com/people/PMroz?et=watches.email.thread> in
Discussions - View the full discussion
<https://community.jmp.com/message/212673?et=watches.email.thread#212673>
Thank you so much! The Sum() function worked beautifully. I cannot
believe I spent several hours yesterday researching something that turned
out to be so easy!
I am still struggling with the If ( Is Missing function. I would like to
figure that out eventually. However, I am now able to move forward with my
project.
Thank you again,
From: jeff.perkinson
Sent: Wednesday, August 20, 2014 11:55 AM
reply from jeff.perkinson
<https://community.jmp.com/people/jeff.perkinson?et=watches.email.thread>
in Discussions - View the full discussion
<https://community.jmp.com/message/212674?et=watches.email.thread#212674>
The SUM function is the easiest way to deal with this problem - didn't think of that one - thanks Jeff!
With regard to the IF in the formula box, with a formula of this complexity, it's easiest to just double-click on the formula box itself and copy/paste the code.
Great…Thank you very much! Desta
Sent: Wednesday, August 20, 2014 1:19 PM
<https://community.jmp.com/message/212675?et=watches.email.thread#212675>
You can get rid of the "Else" clause by selecting the "else clause" and clicking the button in the keypad at the top of formula editor.
Once I did the copy/paste, I get it! That is really cool! I made notes so
that I can use this idea again. I’m sure there will be an application for
it eventually. Thank you so much. I am a new JMP user trying to teach
myself the software. The next time I run into trouble, I will be sure to
ask the “user community.” So much less frustrating than spending hours
with trial and error. Thanks again. Desta