Help please, trying to get a function to detect 4 cells in a row.

**KatenKotsu** · 06-17-2012, 05:54 AM

I'm trying to get Microsoft Excel to check to see if this variable is true or not. Example would be that I would flip 10 coins and what I'd need it to do is to get it to tell me that if somewhere within those 10 coin flips, if 4 of them where in a row (not total).

Example would be: (H for Heads, T for Tails)

THTTHHHHTTT <--- How would I get Excel to look at that (in 10 different cells going from A-J) and tell me "yes, no, true or false?

Thank you for taking the time to read this.

**galvinpaddy** · 06-17-2012, 06:47 AM

Hello,
If your information is in Cell A1, in CellA2 please type -

Please Login or Register  to view this content.

galvinpaddy

**galvinpaddy** · 06-17-2012, 06:49 AM

You could also put in -

Please Login or Register  to view this content.

that will show you OK if either HHHH or TTTT exist, but it will only check if 4 or more exist in a row, not if its ONLY 4

**KatenKotsu** · 06-17-2012, 07:03 AM

Is there any way to search multiple cells? For some reason (even after changing the name from HHHH to what I needed) it continues to say Not OK.

**galvinpaddy** · 06-17-2012, 07:29 AM

The part that says 'A1' you need to change that to reflect what range you are after.

**galvinpaddy** · 06-17-2012, 07:32 AM

If you load a sample sheet I may be able to help more.

**Cutter** · 06-17-2012, 08:39 AM

I think you'd need to CONCATENATE() the results into one cell before using those formulas on that cell.

**galvinpaddy** · 06-17-2012, 08:41 AM

Agreed, my original post assumed all info was in one cell (A1) however, after re-reading the OP i now see it states is different cells! (sry for the confusion)
CONCATENATE() is indeed what you need.

**Marcol** · 06-17-2012, 11:32 AM

There is a way using MAX() & FREQUENCY(), but I can't get my head around it ...

Try this SUMPRODUCT() attempt, it counts the number of times there are 4 consecutive matching characters, including overlaps, i.e. if there are 10 consecutive matching characters, the formula will return 7.
[EDIT]
See the second table, it highlights the first of 4 consecutive matching characters.

In A2

Please Login or Register  to view this content.

Select from the dropdown in A1

**jason.b75** · 06-17-2012, 11:57 AM

Originally Posted by KatenKotsu

THTTHHHHTTT <--- How would I get Excel to look at that (in 10 different cells going from A-J) and tell me "yes, no, true or false?

11 coin flips in 10 cells could confuse the formula

Assuming that was a typo, try the formula below array confirmed with Shift Ctrl Enter,

Please Login or Register  to view this content.

**Ace_XL** · 06-17-2012, 12:38 PM

Good day,

Firstly, in column K get all values of A-J together using

Please Login or Register  to view this content.

Then use the following to determine whether there are 4 consecutive instances

Please Login or Register  to view this content.

**Marcol** · 06-17-2012, 03:43 PM

I got a bee in my bunnet with this thread ...

Compare the methods SUMPRODUCT(),FREQUENCY(),MATCH(), and CONCATENATE() in this workbook

Select from the drop-down in A1 to find out if there is a series of 4 or more Heads/Tails in a row.
The second column in each case indicates if there is a series of 4 or more of either in the row.

**jason.b75** · 06-18-2012, 12:45 PM

Some revised formula for you to play with Marcol

These are all for H / T combined check

=SUMPRODUCT((A2:J2={"T";"H"})*(B2:K2={"T";"H"})*(C2:L2={"T";"H"})*(D2:M2={"T";"H"}))>0

{=OR(ISNUMBER(MATCH(REPT({"H","T"},4),A2:J2&B2:K2&C2:L2&D2:M2,0)))}

=OR(ISNUMBER(SEARCH(REPT({"T","H"},4),A2&B2&C2&D2&E2&F2&G2&H2&I2&J2)))

The same method won't work with the frequency formula.

And another formula that came to mind

=MAX(COUNTIF(OFFSET($A2:$G2,,{0,1,2,3,4,5,6},1,4),{"T";"H"}))=4

I'm sure there are many other ways to get the same results.

edit:-

If you try to analyse the formula construction note that the array constants in the sumproduct and countif formula are vertical, a horizontal array would give incorrect results.

**Marcol** · 06-19-2012, 03:45 AM

Good stuff Jason ...

Help please, trying to get a function to detect 4 cells in a row.

LinkBack

Thread Tools

Rate This Thread

Display

Help please, trying to get a function to detect 4 cells in a row.

Re: Help please, trying to get a function to detect 4 cells in a row.

Re: Help please, trying to get a function to detect 4 cells in a row.

Re: Help please, trying to get a function to detect 4 cells in a row.

Re: Help please, trying to get a function to detect 4 cells in a row.

Re: Help please, trying to get a function to detect 4 cells in a row.

Re: Help please, trying to get a function to detect 4 cells in a row.

Re: Help please, trying to get a function to detect 4 cells in a row.

Re: Help please, trying to get a function to detect 4 cells in a row.

Re: Help please, trying to get a function to detect 4 cells in a row.

Re: Help please, trying to get a function to detect 4 cells in a row.

Re: Help please, trying to get a function to detect 4 cells in a row.

Re: Help please, trying to get a function to detect 4 cells in a row.

Re: Help please, trying to get a function to detect 4 cells in a row.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions