Need help with the formula

Question

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.

Mark_Bailey · Accepted Answer

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.

Jeff_Perkinson · Accepted Answer

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