count consecutive number and sum last 100 entry

J

Josephoo

New Member
Joined
Dec 7, 2015
Messages
22
Hi

I have been looking at this for 1 week and still cannot find an idea on how I can solve this. Some help would be greatly appreciated.

1. I have already achieve this and "count" the number of consecutive for all data. What I'm trying to do is to count the last 100 data.
2. count the consecutive L of last 10 data and sum the value next to L.

ie: SUPPOSE COUNT THE LAST 10 DATA THAT IS CONSECUTIVE "L" AND HIGHEST COUNT.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]row/col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]L[/TD]
[TD]-1[/TD]
[TD]MAX "L" COUNT OF LAST 10 DATA[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]L[/TD]
[TD]-1[/TD]
[TD]MAX "L" COUNT OF LAST 10 DATA AND SUM THE VALUES NEXT TO THEM[/TD]
[TD]-32[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]L[/TD]
[TD]-1[/TD]
[TD]MAX "L" COUNT OF ALL DATA[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]L[/TD]
[TD]-2[/TD]
[TD]MAX "L" COUNT OF ALL DATA AND SUM THE VALUES NEXT TO THEM[/TD]
[TD]-7[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]L[/TD]
[TD]-2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]W[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]L[/TD]
[TD]-5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]L[/TD]
[TD]-10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]W[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]W[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]W[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]L[/TD]
[TD]-6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]L[/TD]
[TD]-8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]L[/TD]
[TD]-2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]L[/TD]
[TD]-16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]W[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]W[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]L[/TD]
[TD]-9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]W[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]W[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



So far, I have only manage to get D3. using the following formula. Been trying to modify the formula but still can't get my head around it. wants to know how i can get D1, D2 and D4

=MAX(FREQUENCY(IF(IFERROR(A1:A20="L",FALSE),ROW(A1:A20)),
IF(IFERROR(1-(A1:A20="L"),FALSE),ROW(A1:A20))))

Thanks Heaps!
 
Last edited:
Sort by date Sort by votes
With A1:B20 containing your data, try the following...

D1: 10

(This indicates that you want the max consecutive count and corresponding sum for the last 10 cells within the specified range. Change this as desired.)

E1:

=MAX(FREQUENCY(IF(ROW(A1:A20)>=LARGE(ROW(A1:A20),D1),IF(A1:A20="L",ROW(A1:A20))),IF(ROW(A1:A20)>=LARGE(ROW(A1:A20),D1),IF(A1:A20<>"L",ROW(A1:A20)))))

...confirmed with CONTROL+SHIFT+ENTER.

F1:

=SUM(OFFSET(B1:B20,ROWS(B1:B20)-MATCH(TRUE,COUNTIF(OFFSET(A1:A20,ROWS(A1:A20)-ROW(INDIRECT("1:"&D1-E1+1)),0,-E1),"L")=E1,0),0,-E1))

...confirmed with CONTROL+SHIFT+ENTER.

Note: If there's more than one consecutive count that equals the maximum, the last occurrence is taken into account.

Hope this helps!
 
Upvote 0
Thanks Domenic!

Its a great help! I think i see where you are coming from. But I just realized that I have #N/A or #DIV/0! in row A and B. How do i remove the NA or DIV0 and just focus on the real data. I tried cleaning up the data but it returns 0. I clean up by making n/a = 0 or "". Row and And Row b is actually driven by formula that count L and W as well as the corresponding values.

[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]row/col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]L[/TD]
[TD]-1[/TD]
[TD]MAX "L" COUNT OF LAST 10 DATA[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]L[/TD]
[TD]-1[/TD]
[TD]MAX "L" COUNT OF LAST 10 DATA AND SUM THE VALUES NEXT TO THEM[/TD]
[TD]-32[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]L[/TD]
[TD]-1[/TD]
[TD]MAX "L" COUNT OF ALL DATA[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]L[/TD]
[TD]-2[/TD]
[TD]MAX "L" COUNT OF ALL DATA AND SUM THE VALUES NEXT TO THEM[/TD]
[TD]-7[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]L[/TD]
[TD]-2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]W[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]L[/TD]
[TD]-5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]L[/TD]
[TD]-10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]W[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]W[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]W[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]L[/TD]
[TD]-6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]L[/TD]
[TD]-8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]L[/TD]
[TD]-2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]L[/TD]
[TD]-16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]W[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]W[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]L[/TD]
[TD]-9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]W[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]W[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]#N/A[/TD]
[TD]#DIV/0![/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]#N/A[/TD]
[TD]#DIV/0![/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]#N/A[/TD]
[TD]#DIV/0![/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]#N/A[/TD]
[TD]#DIV/0![/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks mubashiraziz. But I'm thinking of adding the iferror into this code.

D1: 10

(This indicates that you want the max consecutive count and corresponding sum for the last 10 cells within the specified range. Change this as desired.)

E1:

=MAX(FREQUENCY(IF(ROW(A1:A20)>=LARGE(ROW(A1:A20),D1),IF(A1:A20="L",ROW(A1:A20))),IF(ROW(A1:A20)>=LARGE(ROW(A1:A20),D1),IF(A1:A20<>"L",ROW(A1:A20)))))

...confirmed with CONTROL+SHIFT+ENTER.

F1:

=SUM(OFFSET(B1:B20,ROWS(B1:B20)-MATCH(TRUE,COUNTIF(OFFSET(A1:A20,ROWS(A1:A20)-ROW(INDIRECT("1:"&D1-E1+1)),0,-E1),"L")=E1,0),0,-E1))

...confirmed with CONTROL+SHIFT+ENTER.
Click to expand...
 
Upvote 0
The formulas can be amended to take into consideration cells containing error values. However, it would make an already somewhat resource intensive formula even more so. It looks like those error values occur at the end of the data. Is this always the case? If so, instead of amending the formulas, we can use dynamic named ranges in order to define our ranges to exclude those error values occurring at the end. So, for example, first define the following...

1) On the Ribbon, select Formulas > Defined Names > Name Manager

Code: 
Click New

Name:  BigNum

Refers to:  =9.99999999999999E+307

Click OK

Click New

Name:  ColA

Refers to:  =$A$1:INDEX($A:$A,MATCH(BigNum,$B:$B,1))

Click OK

Click New

Name:  ColB

Refers to:  =$B$1:INDEX($B:$B,MATCH(BigNum,$B:$B,1))

Click OK

Close

Then try...

D1: 10

E1, confirmed with CONTROL+SHIFT+ENTER:

=MAX(FREQUENCY(IF(ROW(ColA)>=LARGE(ROW(ColA),D1),IF(ColA="L",ROW(ColA))),IF(ROW(ColA)>=LARGE(ROW(ColA),D1),IF(ColA<>"L",ROW(ColA)))))

F1, confirmed with CONTROL+SHIFT+ENTER:

=SUM(OFFSET(ColB,ROWS(ColB)-MATCH(TRUE,COUNTIF(OFFSET(ColA,ROWS(ColA)-ROW(INDIRECT("1:"&D1-E1+1)),0,-E1),"L")=E1,0),0,-E1))

E2, confirmed with CONTROL+SHIFT+ENTER:

=MAX(FREQUENCY(IF(ColA="L",ROW(ColA)),IF(ColA<>"L",ROW(ColA))))

F2, CONTROL+SHIFT+ENTER:

=SUM(OFFSET(ColB,ROWS(ColB)-MATCH(TRUE,COUNTIF(OFFSET(ColA,ROWS(ColA)-ROW(INDIRECT("1:"&ROWS(ColA)-E2+1)),0,-E2),"L")=E2,0),0,-E2))

A1:F24

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl63, width: 64"]L[/TD]
[TD="class: xl63, width: 64, align: right"]-1[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64, align: right"]10[/TD]
[TD="class: xl63, width: 64, align: right"]4[/TD]
[TD="class: xl63, width: 64, align: right"]-32[/TD]
[/TR]
[TR]
[TD="class: xl63"]L[/TD]
[TD="class: xl63, align: right"]-1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]-7[/TD]
[/TR]
[TR]
[TD="class: xl63"]L[/TD]
[TD="class: xl63, align: right"]-1[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]L[/TD]
[TD="class: xl63, align: right"]-2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]L[/TD]
[TD="class: xl63, align: right"]-2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]W[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]L[/TD]
[TD="class: xl63, align: right"]-5[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]L[/TD]
[TD="class: xl63, align: right"]-10[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]W[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]W[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]W[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]L[/TD]
[TD="class: xl63, align: right"]-6[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]L[/TD]
[TD="class: xl63, align: right"]-8[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]L[/TD]
[TD="class: xl63, align: right"]-2[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]L[/TD]
[TD="class: xl63, align: right"]-16[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]W[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]W[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]L[/TD]
[TD="class: xl63, align: right"]-9[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]W[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]W[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]#N/A[/TD]
[TD="class: xl63, align: center"]#DIV/0![/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]#N/A[/TD]
[TD="class: xl63, align: center"]#DIV/0![/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]#N/A[/TD]
[TD="class: xl63, align: center"]#DIV/0![/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: center"]#N/A[/TD]
[TD="class: xl63, align: center"]#DIV/0![/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

Hope this helps!
 
Upvote 0
You're very welcome. And thanks for the feedback.

Cheers!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Trying to get a formula to show last 5 most recent text values in a column corresponding to a name
2
Replies
15
Views
187
Peter_SSs
Peter_SSs
V
  • Solved
I need help with this sumif formula:
Replies
4
Views
143
valmir
V
M
  • Question Question
Ecel formula for working hours and overtime calculation
Replies
3
Views
78
Sam_D_Ben
Sam_D_Ben
gtd526
  • Solved
adding .5 to existing Countif with Criteria
Replies
3
Views
90
Peter_SSs
Peter_SSs
K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
174
Sergius
S

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top