Solved

Count "x" number in a row

Posted on 2012-04-11
32
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 9
  • 7
  • +3
32 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37835515
What is the answer for the above example?
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 37835531
So I guess the answer is 6.
0
 

Author Comment

by:spudmcc
ID: 37835543
Yes, it would be six.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:redmondb
ID: 37835545
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
ID: 37835601
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
ID: 37835662
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
ID: 37835703
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
ID: 37835792
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
ID: 37835836
... 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 81

Expert Comment

by:byundt
ID: 37835873
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
ID: 37836406
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
ID: 37836550
=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
ID: 37836706
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
ID: 37836722
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
ID: 37836753
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
 
LVL 26

Expert Comment

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

Expert Comment

by:Rory Archibald
ID: 37836798
For that syntax, yep.
0
 

Author Comment

by:spudmcc
ID: 37836888
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
ID: 37836922
Andy,

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

Regards,
Brian.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37836926
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
ID: 37836945
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
ID: 37836951
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
ID: 37836979
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
ID: 37836985
I think a points split would be more appropriate since Brian gave you several options for solutions?
0
 

Author Comment

by:spudmcc
ID: 37837015
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
ID: 37837019
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
ID: 37837080
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
ID: 37837143
Thanks, Brad.
0
 

Author Closing Comment

by:spudmcc
ID: 37837145
Thanks Experts for the help!  

Andy
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37837169
you're welcome. :)
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37837281
Thanks all!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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