 View Question
Q: Microsoft Excel Formulae ( Answered,   0 Comments ) Question
 Subject: Microsoft Excel Formulae Category: Computers Asked by: kennyrg-ga List Price: \$10.00 Posted: 15 Jun 2006 04:14 PDT Expires: 15 Jul 2006 04:14 PDT Question ID: 738332
 ```I need to find a formula for microsoft excel which will allow me to create a variable which tells me whether each row of my data satsfies the requirement that it have a certain number of the same consecutive values. Bearing in mind that my data were originally continuous baseline climate data that i have recategorised into a binary variable (0=not suitable, 1=suitable) and i have thousands of rows and 12 columns (corresponding to 12 months). I need to know which rows have x consecutive months of suitable climate... Since the data is baseline, the consecutive months can run from dec through to jan as this is averaged data.``` ```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.  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.  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 . 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```  