BookmarkSubscribe
Choose Language Hide Translation Bar
dledom
Community Trekker

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.

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.

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

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.

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)

0 Kudos
8 REPLIES 8
pmroz
Super User

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.

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)

0 Kudos
Jeff_Perkinson
Community Manager Community Manager

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.

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

-Jeff
dledom
Community Trekker

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.

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,

Desta

From: jeff.perkinson

Sent: Wednesday, August 20, 2014 11:55 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 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>

0 Kudos
dledom
Community Trekker

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.

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>

0 Kudos
pmroz
Super User

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.

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.

dledom
Community Trekker

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.

Great…Thank you very much! Desta

From: PMroz

Sent: Wednesday, August 20, 2014 1:19 PM

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/212675?et=watches.email.thread#212675>

0 Kudos
dledom
Community Trekker

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.

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

From: PMroz

Sent: Wednesday, August 20, 2014 1:19 PM

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/212675?et=watches.email.thread#212675>

0 Kudos
Jeff_Perkinson
Community Manager Community Manager

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.

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.

-Jeff

-Jeff
0 Kudos