Skip to main content

Count Consecutive Numbers....



Hi One and All,
This articles demonstrates that how can many times a number has repeated consecutively. So here is the explanation:-
The Array formula I have used is this, which has been dragged down..
=IF(AND(A4<>0,A5=0,A6=0),MATCH(FALSE,A5:A$16=0,0)-1,"") ----(1)
First I will explain you the And() part of the formula…
i.e.
AND(A4<>0,A5=0,A6=0)

Actually it is a trigger, that tells excel when to throw the count as a result..
like this..

when A4<>0
A5=0 then count zeros otherwise show ""
A6=0
I think, One conclusion can be easily drawn that when First cell(i.e. A4) is not equal to zero, AND Second cell is equal to zero AND third cell is equal to zero.. (means there are two consecutive zeros) then count the zeros other wise show nothing ("")
This is to avoid  the excel regenerating the result. As you can see it has generated the result only two times.

The purpose of it will be more evident when we will try to understand the next part i.e. Match()
i.e.
MATCH(FALSE,A5:A$16=0,0)-1
Actually it performs when the AND condition fulfills and one more thing you can see is that in each formula above, the bold part in Match formula i.e.MATCH(FALSE,A5:A$16=0,0)-1 is always equal to the 2nd Condition in AND formula i.e. A5=0

Actually when AND condition fulfills, Match function finds the row no. of the very next Non Zero value in the range starting from the first cell containing zero i.e. second condition cell in AND formula.

here is the explanation
MATCH(FALSE,A5:A$16=0,0)-1 will result in 
MATCH(FALSE,{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE},0)-1
which will result in 4 and  4 - 1 = 3

The same formula can be used to count other strings and numbers as well by replacing zero.

For more clarification, I think FORMULA AUDITING is the best tool..

Regards,

Excel file can be downloaded here..

Labels:

Comments

Post a Comment

Popular posts from this blog

Automation:- Sending Invitation to Meeting Using Excel VBA

Hello Everyone, In one of the previous post, I wrote about automating Sending Emails using Excel VBA. This time I have come up with a pretty similar code. The code below sends Outlook Meeting Invitations to recipients on one click. here is the Code:- Sub Send_Invite_Auto() Dim olApp As Outlook.Application Dim olApt As AppointmentItem Set olApp = New Outlook.Application 'Creating Outlook Session Set olApt = olApp.CreateItem(olAppointmentItem) 'Creating an Appointment With olApt .Subject = "Enter the subject here." 'Subject .Start = DateAdd("d", 5, Now) 'Enter Date + Time here. .Recipients.Add ("example@gmail.com") 'Recipient Name, Alias, or any other Attribute. .MeetingStatus = olMeeting 'olAppointmentItem with Meeting status olMeeting 'becom...
10 comments
Read more

Making Password Protected PDFs using Excel Vba and PDFtk Tool

Hi Everyone, This time, I have come up a VBA Code to generate Password protected PDFs using Excel. Actually, Excel Vba has .ExportAsFixedFormat Method to generate PDFs but this hasn't any Parameter which takes password to protect the PDFs. So I have used PDFtk Tool which provide Command Line Interface to make PDFs protected using Password. Actually, You can do various things using PDFtk Tool command line varying from creating, merging, Protecting and many other. So Download the PDFtk Tool from the following link:- Download PDFtk Tookit Here are the Steps:- 1. Install the PDFtk Toolkit. 2. Use the following code to Print or Export the Activesheet with a password.
8 comments
Read more

Highlighting Duplicates across multiple sheets

Hi One and All, This time I have come up with some conditional formatting stuff. The aim is to highlight the duplicates across multiple sheets and with in the sheet as well. Assumptions:- 1. I am assuming that sheet names goes on like sheet1, sheet2, sheet3.... 2. The Target Column No. is same in all the sheets. I mean, as in the attached example, its Column A which is being targeted in both sheets.
2 comments
Read more