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
- :
- Count how many time Y is greater than N

Topic Options

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

Highlighted
##

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

Count how many time Y is greater than N

May 16, 2018 5:39 PM
(2811 views)

Is there a formula that can be used to determine the number of rows that contain a value greater than a predetermined number, like > 65%?

I guess it would be similar to a countif formula in excel. I want to count if the value is greater than 65%.

Thank you

7 REPLIES 7

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

Re: Count how many time Y is greater than N

`col sum(if(:your target column > .65, 1, 0));`

and this can be shortened to:

`col sum(:your target column > .65);`

Jim

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

Re: Count how many time Y is greater than N

Of course, if you really only care about knowing the number – as opposed to using it in a further formula – the fastest way to find this is to use the Data Filter (**Rows**->**Data Filter**) and then use the filter to select the rows where the value is greater that 65%. You can then see the number of selected rows in the data table.

-Jeff

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

Re: Count how many time Y is greater than N

Is there a way to do this where 0.65 is not a fixed value but rather the ith value in an adjacent row? Meaning - could I have another cloumn that is a list of percentages and for every row I calculate the sum of the target column greater than the percentage of the ith row of the percentage column?

Something like:

`col sum(:your target column > percentage column);`

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

Re: Count how many time Y is greater than N

Do you want to only add up the rows where the target column's value is greater than a percentage columns value?

`col sum( If(:target column > :percentage column, :target column, 0 );`

Jim

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

Re: Count how many time Y is greater than N

yes except I have the complication that the target column is the same as the percentage column. So as I go down the row - I want the value int hat row to act as the threshold for the column

I attached an example data file. I have gotten it to work with summation but it's terribly slow/inefficient and won't work on my real data of >3M rows.

The second column is the summation formula. The third column is the one I want to work. The fourth and fifth columns are variations of the third using fixed values and those evaluate properly for that particular value I put in.

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

Re: Count how many time Y is greater than N

That sounds a lot like the rank. Col Rank() basically give you the number of items less than a given item (plus 1). For ties, you'd have to use Col Rank(:x, <<tie("minimum")). But you want greater than, so you could subtract the rank from the total number and also substract off the number of equal values:

`Col Number( :Binned Temperature )`

- Col Rank( :Binned Temperature, <<tie( "minimum" ))

- Col Number( :Binned Temperature, :Binned Temperature )

+ 1

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

Re: Count how many time Y is greater than N

This is great - thanks!