Assume a database where customers are listed from cell A6 down.  From cell B5 to the right months are entered from April to March (B5:M5).  In B6:M6 (Customer 1), a user enters 1’s and 0’s.  A value of 1 respresents “Cheque bounced” and 0 represents “Cheque honoured”.  Similar data is entered for other customers in B7:M500.

One may want to know the maximum number of consecutive “Cheque bounce events” for all customers listed in column A without using spare rows and columns.

In cell N6, enter the following array formula (Ctrl+Shift+Enter)

=IF(MAX(FREQUENCY(IF(B6:M6=1,COLUMN(B6:M6)),IF(B6:M6=0,COLUMN(B6:M6))))=1,0,MAX(FREQUENCY(IF(B6:M6=1,COLUMN(B6:M6)),IF(B6:M6=0,COLUMN(B6:M6)))))

• Suresh R says:

Hi,

Attached file shows the status of Meter-daywise. It has three status as follows…

* “F” with Red font stating faulty meter.
* “Numbers/Readings” with Green font stating Working.
* “NI” for Meter Not Installed.

I need to calculate unique number of fautly times that meter reported.

For eg:

TAG ID 1 2 3 4 5 6 7 8 9 10 11 Faulty Status
HAGT10547_A 1480 F F 1520 F 1540 F F 3

For this above site, the Meter was working on 1st.
Then on 2nd it went Faulty & it continues to be faulty on 3rd too. This means meter went faulty 1 time(Actual Faulty)
On 5th, it shown reading which means the meter got repaired & working on 5th.
Again on 6th, meter went faulty. Now it goes to second time faulty.
On 8th, it shown reading which means the meter got repaired & working on 8th.
Then on 9th it went Faulty. On 10th no readings taken. Then it continues to be faulty on 11th too. This means meter went faulty 1 time
So the actual faulty count should be “3”.
If I use count formula, I’ll get 5 faulty count. But I need “3” here.

Request you to help me out…

• Hi,

Your data in the table does not appear very clearly. Assume that data is arranged as follows:

1. Cell A5 has the text value “TAG ID”;
2. Range B5:L5 have numbers from 1 to 11;
3. Cell M5 has the text value “Faulty Status”;
4. Cell B6 has 1480;
5. Range C6:D6 has F;
6. Cell F6 has 1520;
7. Cell G6 has F;
8. Cell I6 has 1540;
9. Cell J6 has F; and
10. Cell L6 has F

In cell M6, you wish to know the “Unique consecutive” occurences of “F”. In this case, answer should be 3.

In cell M6, try this array formula (Ctrl+Shift+Enter)

=SUMPRODUCT(1*(FREQUENCY(IF(B6:L6=”F”,COLUMN(B6:L6)),IF(ISNUMBER(B6:L6),COLUMN(B6:L6)))>0))

Hope this helps.

• Suresh R says:

Hi Sir,

I checked it… Its coming right!!!! Thks a lot ji….

• Josh says:

Hi,

I have a sheet with four main categories. (this is for a card game, BTW)

Wins (W), 2nd (S), 3rd (T) and the loss is shown as a number (1 – 200+).
Also, there are special Wins (W3) and special 2nd (S3).

I need to calculate the longest winning streak with the W as well as the W3.
I need to calculate the longest winless streak to include S, S3, T and # (loss)
The highest Winless streak is a combination of S, S*, T and # (the highest consecutive number of games without a win).
Then I need the longest losing streak counting the most cells in a row with a number.

It looks a little like this –
A B C D E F G H I J K L M N O P Q R S T U V W X Y
1 Peter T T S S W WW W3 W T W T W T W T
2 Kevin 50 W W S W 45 T T S3 S W3 65 T 50 W3 S S S W
3 Josh S W S S T T S3 W T W 96 S

Peter should have a winning streak of 5, a winless streak of 4 and a losing streak of 0
Kevin should have a winning streak of 2, a winless streak of 5 and a losing streak of 1.
Josh should have a winning streak of 1, a winless streak of 5 and a losing streak of 1

Any advice will be greatly appreciated. It’s getting pretty painful figuring this out manually. 🙂

Thanks,

Josh.

• Josh says:

Hi Ashish,

Thank you very much. That appears to be working.
I had a little glitch, but figured it out.
All of the cells calculate the final results from another worksheet. If a player doesn’t play the false logic value was shown as a space ” ” , displaying a blank cell. I changed the value “” (no space) to show nothing, and everything else fell into place.

Nice job!

I may pick your brain again someday. 🙂

• Nesdon Booth says:

Ashish, I am trying something similar, and so tried to adapt your array formula, but not understanding it, couldn’t make it work.

Understanding statistics is important for my students, and so I have asked them all to record as many coin tosses as they can bear (at least 100) which I plan to aggregate to analyze patterns in coin tosses.

So I have a single column with n rows with a string of “T”s and “H”s indicating the outcome of n tosses. I want to search for the longest streaks of either toss. This is what I tried for column with 100 tosses:

=SUMPRODUCT(1*FREQUENCY(IF(A1:A101="T",ROW(A1:A101)),IF(ISNUMBER(A1:A101),ROW(A1:A101)))>0)

I am running excel 2011 for mac v. 14.3.2 on mac osx 10.6.8

• Hi,

• Hi,

I am not sure of what is the Mac equivalent of Ctrl+Shift+Enter (Array formula). When I array enter (Ctrl+Shift+Enter) these formulas in cell D4:D5 of sheet2, I get the result as 6 for Tails (range A21:A26) and 9 for Heads (range A74:A82).

Array formula used in cell D4:

=IF(MAX(FREQUENCY(IF(A1:A100=”T”,ROW(A1:A100)),IF(A1:A100=”H”,ROW(A1:A100))))=”T”,0,MAX(FREQUENCY(IF(A1:A100=”T”,ROW(A1:A100)),IF(A1:A100=”H”,ROW(A1:A100)))))

Array formula used in cell D5:

=IF(MAX(FREQUENCY(IF(A1:A100=”H”,ROW(A1:A100)),IF(A1:A100=”T”,ROW(A1:A100))))=”H”,0,MAX(FREQUENCY(IF(A1:A100=”H”,ROW(A1:A100)),IF(A1:A100=”T”,ROW(A1:A100)))))

Once again, these formulas have to be confirmed with Ctrl+Shift+Enter

Hope this helps.

• Hi,

Try this array formula to count the maximum streak of H’s

=IF(MAX(FREQUENCY(IF(A1:A101="T",ROW(A1:A101)),IF(A1:A101="H",ROW(A1:A101))))="T",0,MAX(FREQUENCY(IF(A1:A101="T",ROW(A1:A101)),IF(A1:A101="H",ROW(A1:A101)))))

• Dani says:

I’m posting this comment as you asked me to:

Hi! I have this data on range C2:C29

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

I need to find the largest number of consecutive repetitions on a series

So for this data it would be number, which repeats 4 times in a row.

NOTE: So I’m not looking which repeats the most in general (cause in that case I would look for nuber 1 which repeats 8 times), but in a consecutive way (which is number 1, which repeats 4 times).

NOTE: For the record, I’ve tried with sumproduct and countif with no luck. Maybe I’m not doing it right!

Thank you so much for your time!!

Dani

• Hi,

Assuming you have only 1’s and 0’s in the range, enter this array formula (Ctrl+Shift+Enter) in cell E2

=MAX(IF(MAX(FREQUENCY(IF(C2:C13=1,ROW(C2:C13)),IF(C2:C13=0,ROW(C2:C13))))=1,0,MAX(FREQUENCY(IF(C2:C13=1,ROW(C2:C13)),IF(C2:C13=0,ROW(C2:C13))))),IF(MAX(FREQUENCY(IF(C2:C13=0,ROW(C2:C13)),IF(C2:C13=1,ROW(C2:C13))))=0,0,MAX(FREQUENCY(IF(C2:C13=0,ROW(C2:C13)),IF(C2:C13=1,ROW(C2:C13))))))

Hope this helps.

• Deepak Lokhande says:

Dear Sir,

I want to hightlight the data where in the text value consecutive occurance in 10

• Nathan K. says:

Hello,

As requested, I am reposting this question from another forum.

I am looking to do this exact same function, but I need to ignore blank cells BEFORE the first populated cell.

Empty Empty Empty Data Empty Data Empty Empty Data

For the above example, I would want the result to be ” 2 ” because after the first populated cell, there is a maximum of 2 empty cells between the remaining data. Can this be done with a UDF?

• Nathan K. says:

Ashish,

Thank you for all of your assistance.

• Siddhardha says:

Hai
I have a data of O O P P P P O O P P P O O P P
I want to calculate the max number of continuous P which occur last in the row. I want the answer a 2.
I have checked this formula but it is working only for Numbers
=MAX(0,LOOKUP(2,1/(B2:BA2=1),COLUMN(B2:BA2))-LOOKUP(2,1/(ISNUMBER(B2:BA2)*(B2:BA2=0)),COLUMN(B2:BA2)))

Could you please suggest with example.

• Hi,
Does this work?
IF(MAX(FREQUENCY(IF(B6:M6=”P”,COLUMN(B6:M6)),IF(B6:M6=”O”,COLUMN(B6:M6))))=1,0,MAX(FREQUENCY(IF(B6:M6=”P”,COLUMN(B6:M6)),IF(B6:M6=”O”,COLUMN(B6:M6)))))

• Siddhardha says:

The above suggested formula is not working.
It is showing as error #Value.

• Siddhardha says:

Link Sharing is not possible in our company.

Please find the data mentioned Below.

I have used the same formula as you mentioned
=IF(MAX(FREQUENCY(IF(B10:O10=”P”,COLUMN(B10:O10)),IF(B10:O10=”O”,COLUMN(B10:O10))))=1,0,MAX(FREQUENCY(IF(B10:O10=”P”,COLUMN(B10:O10)),IF(B10:O10=”O”,COLUMN(B10:O10)))))

These are the Values
O O P P P O O P P P O O P P

The result is coming as #VALUE

Actually the result that i required is 2 because at the End 2 P available continuously . If I kept O at last means the count should reset and should show as 0.