Checking n cells above's content

[hanvyj]

I have a function that is a "delay" trigger, at the moment it looks in two columns and when it finds an increase it goes from 0 to 1. It also checks if the three cell above is one, and stays so 1 for 3 cells:



=IF(P23>P22,1,IF(O22=1,IF(AND(O21=1,O20=1,O19=0),0 ,1),0))

So the formula checks if the P column is increasing, if so sets to 1.
If the above value is 1 (column O), it is sets to 1 unless there have been 3 consecutive 1s, in which case its sets to 0 (ie the cells above are 1,1,1,0)

Now, the ammount of consecutive ones it checks at the moment is hard-coded. I'd like to have a cell to specify this but I can't think of a way of doing it.

Can anyone help?

[hanvyj]

Is it possible to say reference a cell based on a value say: "O22 + n" where n=2 would reference O24?

[Catalin.B]

The formula you posted had some errors, i used this:
=IF(AND(P23>P22;SUM(O19:O22)<4);1;0) (for 4 consecutive rows)
To have in a cell the number of consecutive rows with 1 value, You must use sum with offset to accomplish that.
Assuming that in cell M3 you enter the number of consecutive rows with 1 value:
=IF(AND(P23>P22;SUM(OFFSET(O23;-$M$3;0;$M$3;1))<M$3);1;0) . Copy this down. (you must replace formula separators ; to ,)
In this case, OFFSET function creates a range of 1 column, with the number of rows = M3, placed above O23(that is the number of rows above O23: -$M$3)

[hanvyj]

Originally Posted by Catalin.B

The formula you posted had some errors, i used this:
=IF(AND(P23>P22;SUM(O19:O22)<4);1;0) (for 4 consecutive rows)
To have in a cell the number of consecutive rows with 1 value, You must use sum with offset to accomplish that.
Assuming that in cell M3 you enter the number of consecutive rows with 1 value:
=IF(AND(P23>P22;SUM(OFFSET(O23;-$M$3;0;$M$3;1))<M$3);1;0) . Copy this down. (you must replace formula separators ; to ,)
In this case, OFFSET function creates a range of 1 column, with the number of rows = M3, placed above O23(that is the number of rows above O23: -$M$3)

Thank you, offset was what I needed. I'd worked out I would have to use something like sum. It would also need a check that the top value = 0, otherwise sequences like:

1
0
1
1

would be picked up as "3 in a row" and it would set to 0, so a simple AND(SUM(the n above offset)=n-1,n above = 0) works well

Cheers! I'm sure I'll probably be using Offset a lot more, you sure do learn a lot about excel coming across these tricky problems.

[Catalin.B]

Great! Then you can mark your thread as solved (under thread tools, select-mark this thread as solved)