Sam,
I think Iv'e got it
Find a variable number x in column A
When found there must be a C in the row below
Match the variable number x rows above the C
The second variable number must also have a C in the row below
based upon your data any solution looking for a number higher than 3 will
fail because you can't search more than 3 rows back above Row 4 so to get
around this instead of looking back x rows as you ask my formula looks back
only 8 rows and I start in Row 16 to give some breating space..
My variable number is in D1
When you resolve the above problem you can change the formula
change -8 to
D1*-1
change -7 to
(D1*-1)+1
=IF(AND(A16="C",A15=$D$1,OFFSET(A16,-8,0)=$D$1,OFFSET(A16,-7,0)="C"),0,C15+1)
Mike
"Sam via OfficeKB.com" wrote:
Hi All,
I would like a formula that can return a numeric sequential count down a
single column based on content of another column. The count will be
sequential until it meets all the criteria, wherein a zero (0) should be
returned and the count reset, starting at one (1), until the criteria is
again met.
Match criteria in this order:
1) match specific numeric value (input cell for varying criterion); 106.
2) match specific text value, the letter "C" (static criterion); in row
directly below 106.
3) match the same numeric value as in (1) above:106; but this must be exactly
106 rows after matching "C" in (2) above.
4) match specific text value, the letter "C" (static criterion); in row
directly below the second 106.
Criteria met, I would like the value zero (0) to be returned to the cell that
houses the second instance of the text value, letter "C".
The data starts in row 4, column "BR" holds numeric and text data. The
sequential count should be returned down column "BS".
Sample Data:
col BS BR
row4 101 1
row5 102 2
row6 103 3
row7 104 4
row8 C 5
row9 101 6
row10 102 7
row11 103 8
row12 104 9
row13 105 10
row14 106 11
row15 C 12
row16 101 13
row17 102 14
row18 103 15
row19 104 16
row20 105 17
row21 106 18
row22 107 19
row23 108 20
row24 109 21
row25 110 22
row26 C 23
row27 101 24
row28 102 25
row29 103 26
row30 104 27
row31 105 28
row32 106 29
row33 C 30
row34 101 31
row35 102 32
row36 103 33
row37 104 34
row38 105 35
row39 106 36
row40 C 0
row41 101 1
row42 102 2
Expected Results:
Column "BR", Row 40 = 0
Looking for numeric value 106 with "C" on the row below = row 33 and then the
same number of rows (106) directly below the first "C", with a "C" on the
next row = row 40. The sequential count should return zero (0) in column "BR"
row 40.
Thanks
Sam
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200802/1