Excel formula using countif

Question

There are 11 numbers in a column.

I need to build a formula that tells me how many times occurs the following situation: Number is bigger than each of previous four numbers.

In this case the situation occurs 3 times. By:

12 bigger than 5;5;5;5
13 bigger than 12;5;5;5
10 bigger than  5;2;9;5

JNevill · Accepted Answer · 2016-06-16 16:37:46Z

1

Assuming your data starts in A1 you can use if() and max() to do this. Add this to B5 (next to the 12 in your example):

=IF(MAX(A1:A4)<A5, 1, "")

This will put a 1 next to 12. You can copy this formula down to find the other values where this is true.

This works by looking at the MAX() value of the previous 4 values. IF() they are higher than the current value, then it prints a 1.

answered Jun 16, 2016 at 16:37

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

and then you need to take the sum of colum B to get your count of how many times it has happened