turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- I am trying to add 3 columns of continous data and put the resulting sum in a se...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 20, 2014 8:16 AM
(3316 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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**)**

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 20, 2014 11:07 AM
(3143 views)
| Posted in reply to message from Jeff_Perkinson 08/20/2014 12:54 PM

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>

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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>

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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>

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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>

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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