BookmarkSubscribeRSS Feed
dledom

Community Trekker

Joined:

Aug 20, 2014

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.

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz

Super User

Joined:

Jun 23, 2011

Solution

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)

8 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

Solution

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)

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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

Joined:

Aug 20, 2014

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>

dledom

Community Trekker

Joined:

Aug 20, 2014

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>

pmroz

Super User

Joined:

Jun 23, 2011

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

Joined:

Aug 20, 2014

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>

dledom

Community Trekker

Joined:

Aug 20, 2014

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>

Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

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