To count the number of instances a certain value is crossed in excel

This formula returns 2 for your data:

Formula:

Please Login or Register  to view this content.

The range will have to be adjusted to reflect the actual rows that your data is in. Note the first range starts in row 2 and the second range starts in row 1.

COUNTIFS counts the number of occurrences that meet one or more conditions. All conditions must be be for the occurrence to be counted. The conditions in this case are:

A2:A16,">="&0.15

The value is greater than or equal to 0.15

A1:A15,"<"&0.15

The preceding value is less than 0.15



The ranges in a COUNTIFS formula must all be the same shape. In the first condition, it is all values starting with the second value. This is where we look to see if a value reached 0.15. In the second condition, we start with the first value, and go to the next-to-last value. This is where we look to see if a value is less than 0.15. Because both conditions must be true, the occurrence that will be counted if a number is >= 0.15 and the previous number is < 0.15.