Locating a specific event in column

**dclarke** · 05-16-2010, 12:48 PM

I have a column of numbers:
1
2
4
2
3
5
7
8
6
9

Starting from the beginning of the list, I need to be able to find the first and second events where 1 minus the next number equals -4 (1 - 5 = -4). At this point, I will reset the starting point at 5 and repeat the processs (5 - 9 will again equal -4) so I will also need to be able to find 9. It may be important to keep in mind that this list is a random flow of positive and negative numbers so the next number I need to identify my be 4 units smaller (9-4=5).

Thanks in advance...
Dana

**Richard Buttrey** · 05-16-2010, 01:17 PM

Hi,

How about with your numbers starting in A2.

B1: =A2, 
B2: =IF(B1-A2=-4,A2,B1)
C2: =IF(B2<>B1,"Event"&COUNTIF(C1:C$2,"=Event*")+1,"")

then copy B2:C2 down your list.

HTH

**dclarke** · 05-16-2010, 01:43 PM

Richard -- THANKS -- nice and simple solution

**dclarke** · 05-16-2010, 03:55 PM

I have one cell in a report and would like to put the value associated with Event 1 (value = 4) in that cell. I also have another cell that will have the value for Event 2 (value = 8) in that cell. Any suggestions on how to perform that task would be greately appreciated.

_____A______B
1
2
3
4____4____Event 1
5
6
7____8____Event 2
8

Thanks in advance,
Dana