Solved

Count "x" number in a row

Posted on 2012-04-11
32
464 Views
Last Modified: 2012-04-12
Hi Experts

I am looking for a solution for something that seems very simple on the surface but I am not sure if it really is in reality.  

Here's the challenge:
              A      B      C      D      E      F      G      H     I      J     K    L    M   N
Row:      1      1      0      0       1      0      0       0     1    1     0    0    0    1  

I am looking to ONLY count the zero's in the above example when there is 3 in a row, ignoring all others values as well as any zero that isn't 3 in a row.  

Any help would be greatly appreciated as always.  

spudmcc
0
Comment
Question by:spudmcc
  • 12
  • 9
  • 7
  • +3
32 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
What is the answer for the above example?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
So I guess the answer is 6.
0
 

Author Comment

by:spudmcc
Comment Utility
Yes, it would be six.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Hi, spudmcc,
=14-LEN(SUBSTITUTE(SUBSTITUTE(CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1),"000","###"),"#",""))

Open in new window

The above assumes that, for example, four zeros in a row only count as three (because the the three zeros have been "used up" before the fourth one comes along). So for your example, it gives six, but it also gives six for the following...

    1      1      0      0       1      0      0       0     0    1     0    0    0    1  

Is this correct?

Regards,
Brian.
0
 

Author Comment

by:spudmcc
Comment Utility
The amount of items in each row could vary.  It isn't always in columns A-J.  
Also, I should clarify that it should count anything >=3.  So if we have

             A        B        C         D         E         F            G        H      I         J         K         L        M      
Row1    1        0         0         0         0         1           0          0     0        1        0          0         0

Row 2   1        1         0          0        1          1           1          1      1       0        0          0        0

Row 1 would equal 10
Row 2 would equal 4

Sorry for not being clear the first time.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
spudmcc,

Thanks, but we need some more, please!
(1) What is the maximum no. of columns?.
(2) Can we shift the columns to the right, so that the formula is in column A?
(3) When a column isn't being used, what will be in it? (It would be ideal if you could give us a sample file!)

Thanks,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
spudmcc,

Pending answers to the above, please see the attached. A few points...
(1) It handles the first 14 or fewer columns. It can be easily changed to handle more columns.
(2) The cells in column O must have a "1" in them. (Sorry, I'll try to get rid of this.)
(3) The formulas in column P must be array-entered (CTRL-SHIFT-ENTER). Let me know if you need more on this.
(4) I manually checked the results, as best I could. I'm hoping you have some test data as a definitive test!

The formula in P1 is
{=SUM(IF((A1:L1&""="0")*(B1:M1&""="0")*(C1:N1&""="0"),3-IF(D1:O1&""="0",2,0)))}

Open in new window

Regards,
Brian.
Three-in-a-Row.xls
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Oh, I've assumed that this must be a formula. Is that correct? (Of course a macro would be pretty simple!)
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
... and here is that macro. It ignores column O and outputs to column Q...
Option Explicit

Sub Three_in_a_Row()
Dim xLast_Row As Long
Dim i As Long
Dim j As Long
Dim xZeros As Long
Dim xCount As Long

Sheets("Sheet1").Activate

xLast_Row = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row

For i = 1 To xLast_Row
    xCount = 0
    xZeros = 0
    For j = 1 To 14
        If Cells(i, j) = "0" Then
            xZeros = xZeros + 1
            If xZeros = 3 Then
                xCount = xCount + 3
            ElseIf xZeros > 3 Then
                xCount = xCount + 1
            End If
        Else
            xZeros = 0
        End If
    Next
    Cells(i, 17) = xCount
Next

End Sub

Open in new window

0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
Brian,
You did better than you thought. I'm not seeing any need for column O to have a 1, having used Paste Special...Values to compare results with either ones or blanks in column O.

Brad
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Brad,

Oops, I should have said "non-zero" rather than "1". (The "1" was a hang-over from an earlier version.)

The problem is that I need an array of D1:N1's values - but it needs to have 12 entries not 11, hence column O. Any idea how I could do this without the extra column?

Regards,
Brian.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
=SUMPRODUCT((FREQUENCY(IF(A1:N1=0, COLUMN(A1:N1)), IF(A1:N1<>0, COLUMN(A1:N1)))>=3)*(FREQUENCY(IF(A1:N1=0, COLUMN(A1:N1)), IF(A1:N1<>0, COLUMN(A1:N1)))))
array-entered should work. Can be extended for different criteria than 3 quite easily. Barry can probably do it in 10 characters or fewer...
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 250 total points
Comment Utility
Rory,

Nice one! Small change (to handle less than 14 entries as requested by OP - see my last two examples)...
{=SUMPRODUCT((FREQUENCY(IF(A1:N1&""="0", COLUMN(A1:N1)), IF(A1:N1&""<>"0", COLUMN(A1:N1)))>=3)*(FREQUENCY(IF(A1:N1&""="0", COLUMN(A1:N1)), IF(A1:N1&""<>"0", COLUMN(A1:N1)))))}

Open in new window

Regards,
Brian.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Ah, my understanding was that there were always values in the cells, but the number of columns might alter, in which case simply changing the ranges is all that's necessary. For blank cells, yes, we need an adjustment like that. :)
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Rory,

"my understanding was that there were always values in the cells"
Yes, you could be right. The exact format is unclear and we're waiting for a response from the OP.

Regards,
Brian.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Trivial point, Rory - can't "SumProduct" be replaced by "Sum"?
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
For that syntax, yep.
0
 

Author Comment

by:spudmcc
Comment Utility
Yes, the number of columns could vary but each cell will always contain a "1" or a "0".  

Andy (Spudmcc)
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Andy,

Thanks. So how should the formula recognise the number of columns?

Regards,
Brian.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Do you mean a different number of columns in use for each row, or can you simply adjust the ranges in the formulas provided earlier to match your data?
0
 

Author Comment

by:spudmcc
Comment Utility
Each row will have the same number of columns but workbook to workbook the number of columns may change.  Todays workbook may have 20 columns but tomorrows might have 25.  We only have to be concerned with 1 day at a time and never need to merge books but the solution need to be flexible enough that it can be used with different column numbers.  

I hope that makes some sort of sense.  

A
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
Comment Utility
Well, I'm not sure what you mean by flexible - all you have to do with the formulas is change the ranges to point at the columns you want to check. So if you need to check cols B:X change the formula from

=SUMPRODUCT((FREQUENCY(IF(A1:N1=0, COLUMN(A1:N1)), IF(A1:N1<>0, COLUMN(A1:N1)))>=3)*(FREQUENCY(IF(A1:N1=0, COLUMN(A1:N1)), IF(A1:N1<>0, COLUMN(A1:N1)))))

to

=SUMPRODUCT((FREQUENCY(IF(B1:X1=0, COLUMN(B1:X1)), IF(B1:X1<>0, COLUMN(B1:X1)))>=3)*(FREQUENCY(IF(B1:X1=0, COLUMN(B1:X1)), IF(B1:X1<>0, COLUMN(B1:X1)))))
0
 

Author Comment

by:spudmcc
Comment Utility
This works great!  I assumed I would have to change the range and that isn't any problem at all.  Thanks for all of your hard work, time and patience.  

Andy (Spudmcc)
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
I think a points split would be more appropriate since Brian gave you several options for solutions?
0
 

Author Comment

by:spudmcc
Comment Utility
You are absolutely correct.  Could I please ask the moderator to split the points between rorya and redmondb.  

Thanks Rory!

Andy
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
I've requested mod attention to get this reopened and you can then re-allocate as you wish. :)
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Folks,

I appreciate the effort, but really don't worry about it! Rory's excellent solution did the job - and I learned a lot from it. Win:win!

Regards,
Brian.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Thanks, Brad.
0
 

Author Closing Comment

by:spudmcc
Comment Utility
Thanks Experts for the help!  

Andy
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
you're welcome. :)
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks all!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

My experience with Windows 10 over a one year period and suggestions for smooth operation
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now