cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
ark1019
Level I

Count how many time Y is greater than N

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
txnelson
Super User

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
Jeff_Perkinson
Community Manager Community Manager

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
tamirlance
Level I

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);

 

 

txnelson
Super User

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
tamirlance
Level I

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. 

XanGregg
Staff

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
tamirlance
Level I

Re: Count how many time Y is greater than N

This is great - thanks!