Hi Sam!
Maybe I've misunderstood what it is you're looking for. Let's take a
look at the following example...
Sheet1!B19:H24 contains...
88 89 90 91 92 93 94
75 76 77 78 79 80 81
70 78 79 84 85 90 92
75 80 81 85 87 88 92
74 75 76 77 78 79 80
89 90 91 92 93 94 95
Sheet2!B1:K1 contains..
80 81 82 83 84 85 86 87 88 89
Sheet2!B19:K24 would contain the following results...
0 0 0 0 0 0 0 0 88 89
80 81 0 0 0 0 0 0 0 0
0 0 0 0 84 85 0 0 0 0
0 0 0 0 0 0 0 87 88 0
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
Note that the 0's are only there to help differentiate the columns, and
the #N/A values can be dealt with by either hiding them using
Conditional Formatting or modifying the formula to eliminate them. The
#N/A values are returned when there are no consecutive pairs or, if they
exist, don't fall within the range specified by the input cells.
Now, are these the results your looking for? If not, can you supply the
correct results?
In article ,
"Sam via OfficeKB.com" wrote:
Hi Domenic & Biff,
I've implemented the formula below across the first two columns ("B" and
"C") in Sheet2 but it produces the N/A error on some rows and
unfortunately, still only returns the same 4 consecutive values that the
previous formula returned. I should see 9 values in columns "B" and "C"
that are consecutive.
Column "A" is empty and remembered to enter with CONTROL+SHIFT+ENTER.
Any further ideas and help much appreciated.
Thanks
Sam
-----------------------------------------------------------------------
Here's my second attempt at a solution. This time I hope I've covered
all the bases. I guess we'll see... :)
Sheet2!B19, copied across and down:
=IF(INDEX(Sheet1!$B19:$G19,MATCH(2,1/((Sheet1!$C19:$H19-Sheet1!$B19:$G19=
1)*(Sheet1!$B19:$G19=80)*(Sheet1!$C19:$H19<=89))) )=B$1,SUM(B$1,A19),IF(C
OUNTIF($A19:A19,"0")=1,SUM(A19,1),""))
...confirmed with CONTROL+SHIFT+ENTER. Note that the same caution
applies here. Column A, starting at A19, must either be blank or
contain text values.
|