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
- :
- Need help with the formula

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

Mar 10, 2017 8:40 AM
(1702 views)

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.

- Tags:
- Formula help

2 ACCEPTED SOLUTIONS

Accepted Solutions

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

Mar 10, 2017 9:23 AM
(3377 views)

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!

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

Mar 10, 2017 10:49 AM
(3369 views)

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...

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

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

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.

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.

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

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

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

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

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

Mar 10, 2017 9:23 AM
(3378 views)

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!

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

Mar 10, 2017 10:49 AM
(3370 views)

@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...

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

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

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.

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.

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

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

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

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

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

Mar 10, 2017 11:35 AM
(1676 views)