CODEWITHSUNDEEP
excel-formula
LubZa
LubZa

Reputation: 13

Excel formula using countif

There are 11 numbers in a column.

5 
5
5
5
12
13
5
9
2
5
10

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

Upvotes: 1

Views: 43

Answers (1)

JNevill
JNevill

Reputation: 50019

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.

Upvotes: 1

Related Questions