Google Answers Logo
View Question
Q: Microsoft Excel Formulae ( Answered,   0 Comments )
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.
Subject: Re: Microsoft Excel Formulae
Answered By: maniac-ga on 15 Jun 2006 16:53 PDT
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)
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
for another answer / clarification that addresses an alternative for
the macro security settings (using a "signed" macro / security

Now, you can refer to that function with a reference like:
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.


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
            Match = 0
        End If
    Next C
    IsSatisfactory = RV
End Function
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy