BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
Community Trekker

## formula for data analysis to exclude certain columns

I'm completing a research study with a test design.  The first part of the survey/test is a questionaire asking about demographics and age questions.  Then I have "Test-like" questions and based on the answer provided, the respondent is given a certain number of points.  For the first set of cases, there are 4 questions and the total amount of points for all 4 questions is 10 total points. I created a new column and created a formula based on the answers and points they were given.  This column shows the total amount of points for all 4 questions and what each respondent has.  I want to see how many respondents scored >8 points, how many respondents scored >5 points, and how many respondents scored < 5 points.  The problem I am facing is differentiating between the people who actually went through the questions and answered them wrong and got 0 points, versus the ones who did not answer any of the questions at all and got 0 points.  I was going to differentiate between the two by looking to see who answered the first test question.  If they reached the first test question and provided an answer, it was assumed that they began the test.

Basically I want to know, how do I exclude those who did not provide an answer for a certain column (first question) in my distribution analysis for another column (total scores for the case)?

I know this is confusing.  Let me know if you have any questions.

3 REPLIES 3
Super User

## Re: formula for data analysis to exclude certain columns

If I understand your request, this is a common question.  However,  I am not sure if I understand your table layout.

If your have Q1-Q4 as columns  you can create another column "N Answered" with the formula

``!IsMIssing(:Q1) + !IsMIssing(:Q2) + !IsMIssing(:Q3) + !IsMissing(:Q4)``

If all 4 questions were answered then this new column will have the value 4. Depending upon what you want, you could look at the distribution by "N Answered" or exclude rows where "N Answered" == 0, etc., I suggest you set the the new column "N Answered"  to Nominal modeling type.

I   call this type of column a "use" column, it determines whether results will be used, or for selection, etc.

Community Trekker

## Re: formula for data analysis to exclude certain columns

Thanks for the response. I was unable to enter that formula into a new column. Your response indicated "!IsMissing(:Q1) + !IsMissing(:Q2) ..." How do I enter this into Jmp?

Super User

## Re: formula for data analysis to exclude certain columns

Your original post stated you made a formula so I assumed you knew how to do this in JMP.  Also, I assumed your questions had numeric responses.

Attached is a JMP table with randomly generated data:

• ID
• Columns Q1-Q4 with numeric responses
• Columns A1-A4 with character responses
• Column N Answered Numeric using the formula I posted previously, note !IsMissing(:Q1)  (not is missing) is 0 if it is missing, and 1 if not
• Column N Answered Character using an alternate formula  (:A1 != "") + (:A2 != "") + (:A3 != "") + :A4 != ""), when working with characters an empty is an empty string

To build a formula in a JMP table:

• create a new column, give it a name
• right click on the column header and select formula
• use the Formula Editor to build a formula or paste a copied formula into the box.  See Help > Books > Using JMP > Chapter 7 Formula Editor.

To see the formula in the attached table, click on the fat plus sign to the right of the column name in the table columns panel at the left of teh table, or right click on a column header (one of the N Answered columns) and select formula. Either method opens the formula editor.