Hello Kennyrg,
There are at least two methods, one using built in functions, and the
second using a simple Excel macro. I recommend the latter, but
describing both in case you need the flexibility.
[1] Using Excel built in functions.
Assuming your data in columns B (2) through M (13) with the data
starting on the second row and that you want to show rows with 5 (or
more) consecutive "suitable" values, enter the following function into
the A2 cell: (split into several lines to clarify the structure)
=SUM(IF(SUM(B2:F2)=5,1,0),
IF(SUM(C2:G2)=5,1,0),
IF(SUM(D2:H2)=5,1,0),
IF(SUM(E2:I2)=5,1,0),
IF(SUM(F2:J2)=5,1,0),
IF(SUM(G2:K2)=5,1,0),
IF(SUM(H2:L2)=5,1,0),
IF(SUM(I2:M2)=5,1,0))>0
Select the first column (less the header line) and fill down to apply to each row.
The way this works is that each inner SUM() function checks to see if
all five consecutive cells (out of five) are non zero. If this is
true, the IF statement returns 1, if not the IF statement returns
zero. The outer SUM adds all of these, and if that result is non zero,
the value is "TRUE", otherwise "FALSE". I've tried this with a variety
of patterns and it appears to work OK.
If you need six values to be "satisfactory" instead of five, reduce
the number of inner SUM functions by one and adjust the regions to
cover six cells each instead of one. In a similar way you can adjust
this for any number of consecutive "satisfactory" values between two
and eleven.
[2] Using a macro
At the end of the answer is a macro you can copy / paste into Visual
basic by doing the following steps:
a. Open the worksheet (or have it already open)
b. Use the menu
Tools -> Macros -> Visual Basic Editor
to bring up Visual Basic. At this point, the upper left should have a
"project" window.
c. Select your worksheet in the project window and then use the menu
Insert -> Module
to bring up a module (code) window.
d. Copy / paste the macro into the module window.
e. Make adjustments if necessary (e.g., if you want to change the
conditions), then Quit & Return to Microsoft Excel (the exact name /
location of that menu item varies by Excel / VB version) to return to
the spreadsheet.
The macro will be saved with the spreadsheet when the spreadsheet is
saved. Note also that you may have to adjust your macro security
settings to "medium" (or less) to run the spreadsheet. See
http://answers.google.com/answers/threadview?id=736450
for another answer / clarification that addresses an alternative for
the macro security settings (using a "signed" macro / security
certificate).
Now, you can refer to that function with a reference like:
=IsSatisfactory(B2:M2,5)
and you should get a TRUE / FALSE result similar to [1]. The first
argument (R) is the range of cells to check, the second argument (N)
is the number of consecutive cells that must be greater than zero.
This macro stops as soon as it determines a TRUE value and runs pretty
quickly on my system (appears instantaneous for the five rows I tried
it on).
Both methods work with the sample data I've tried (several different
TRUE / FALSE patterns). Please make a clarification request if some
part of the answer is incomplete or unclear. I would be glad to help
you further with this question if needed.
Good luck with your work.
--Maniac
Public Function IsSatisfactory( _
R As Excel.Range, _
N As Integer) As Boolean
'
' IsSatisfactory created by Maniac on 06/15/2006
' Returns true if "N" consecutive values are non zero in range R
'
RV = False
Match = 0
For Each C In R
If C.Value > 0 Then
Match = Match + 1
If Match >= N Then
RV = True
Exit For
End If
Else
Match = 0
End If
Next C
IsSatisfactory = RV
End Function |
