Subscribe Bookmark RSS Feed

Need help with the formula

quest74

New Contributor

Joined:

Mar 7, 2017

I have a column of 500 rows in my table with assay values. I like to find out how many of them are within the range 90-100. Is there any way I can add if command formula like in excel or sth like that to say if a certain cell value is >=90 or <= 100 give me "no" or yes and generate another column for this yes, no's. Or any other way I can solve this. All I want to do to get how many cell values are within that range. Pls some help me.

2 ACCEPTED SOLUTIONS

Accepted Solutions
markbailey

Staff

Joined:

Jun 23, 2011

Solution

An easy, interactive solution is the Data Filter. Select Rows > Data Filter. Select the assay column and click Add. Change the lower limit to 90 and the upper limit to 100. The Rows panel in the lower left corner of the data table will indicate the number of selected rows that meet your criterion.

Learn it once, use it forever!
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Solution

@markbailey gave you the fastest way to get the number you're looking for (how many rows are between 90 and 100), but to answer the question you asked about creating a formula here's the formula you're looking for.

 

The screenshots below are from JMP 13. Earlier versions of JMP look a little different but it works just the same.

 

First add a new column using Cols -> New Columns...

 

Parallels DesktopScreenSnapz021.png

In the dialog that results, change the Data Type to Character and click on the Column Properties menu and choose Formula.

 

Parallels DesktopScreenSnapz022.png

That will bring up the Formula Editor. Click on the Conditional functions and choose If.

 

Parallels DesktopScreenSnapz023.png

Now click Comparison. You'll notice that there isn't an a <= b <= c but that's okay we can fix that in just a second. For now choose either of the double sided comparisons.

Parallels DesktopScreenSnapz024.png

Now double-cick on comparison that just got added to the formula and you'll get a text editor box. Just add the = sign in the right place.

Parallels DesktopScreenSnapz025.png

Fill in the "Yes" and "No" on the right side of the conditionals.

Parallels DesktopScreenSnapz026.png

Click Apply and you'll have a column with Yes and No as you originally asked. 

 

Parallels DesktopScreenSnapz027.png

 

Now you can use Distribution to see the number of Yes and No.

Parallels DesktopScreenSnapz028.png

I'm attaching the data table that I built for this example so you can see it for yourself.

 

Let us know if you have any questions or difficulties.

 

-Jeff
3 REPLIES
markbailey

Staff

Joined:

Jun 23, 2011

Solution

An easy, interactive solution is the Data Filter. Select Rows > Data Filter. Select the assay column and click Add. Change the lower limit to 90 and the upper limit to 100. The Rows panel in the lower left corner of the data table will indicate the number of selected rows that meet your criterion.

Learn it once, use it forever!
Jeff_Perkinson

Community Manager

Joined:

Jun 23, 2011

Solution

@markbailey gave you the fastest way to get the number you're looking for (how many rows are between 90 and 100), but to answer the question you asked about creating a formula here's the formula you're looking for.

 

The screenshots below are from JMP 13. Earlier versions of JMP look a little different but it works just the same.

 

First add a new column using Cols -> New Columns...

 

Parallels DesktopScreenSnapz021.png

In the dialog that results, change the Data Type to Character and click on the Column Properties menu and choose Formula.

 

Parallels DesktopScreenSnapz022.png

That will bring up the Formula Editor. Click on the Conditional functions and choose If.

 

Parallels DesktopScreenSnapz023.png

Now click Comparison. You'll notice that there isn't an a <= b <= c but that's okay we can fix that in just a second. For now choose either of the double sided comparisons.

Parallels DesktopScreenSnapz024.png

Now double-cick on comparison that just got added to the formula and you'll get a text editor box. Just add the = sign in the right place.

Parallels DesktopScreenSnapz025.png

Fill in the "Yes" and "No" on the right side of the conditionals.

Parallels DesktopScreenSnapz026.png

Click Apply and you'll have a column with Yes and No as you originally asked. 

 

Parallels DesktopScreenSnapz027.png

 

Now you can use Distribution to see the number of Yes and No.

Parallels DesktopScreenSnapz028.png

I'm attaching the data table that I built for this example so you can see it for yourself.

 

Let us know if you have any questions or difficulties.

 

-Jeff
quest74

New Contributor

Joined:

Mar 7, 2017

You guys are amazing. Thanks a lot to both of you.