Subscribe Bookmark RSS Feed

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.

dledom

Community Trekker

Joined:

Aug 20, 2014

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
Solution

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
Solution

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

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

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

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

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

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

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

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