Checking n cells above's content

hanvyj · #1 03-22-2012, 02:53 AM

Quote:

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 · #2 03-22-2012, 03:58 AM

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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Multiple VLOOKUP's checking multiple Cells	OTPM	Excel	11	05-23-2011 11:18 AM
Help with checking available times	Deepy	Excel	1	02-14-2011 05:06 PM
Count range cells eliminating merge cells	danbenedek	Excel	0	06-15-2010 12:40 AM
Checking for DOS executions	ajetrumpet	Excel	0	12-06-2009 02:33 PM
Package for CD with links to Windows media player content and Flash Shockwave content	hectorh	PowerPoint	4	10-15-2009 12:22 PM