count consecutive number and sum last 100 entry

[Josephoo]

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.

row/col	A	B	C	D
1	L	-1	MAX "L" COUNT OF LAST 10 DATA	4
2	L	-1	MAX "L" COUNT OF LAST 10 DATA AND SUM THE VALUES NEXT TO THEM	-32
3	L	-1	MAX "L" COUNT OF ALL DATA	5
4	L	-2	MAX "L" COUNT OF ALL DATA AND SUM THE VALUES NEXT TO THEM	-7
5	L	-2
6	W	4
7	L	-5
8	L	-10
9	W	3
10	W	4
11	W	5
12	L	-6
13	L	-8
14	L	-2
15	L	-16
16	W	6
17	W	8
18	L	-9
19	W	8
20	W	12

<tbody>
</tbody>

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!

 

[Domenic]

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!

 

[Josephoo]

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.

row/col	A	B	C	D
1	L	-1	MAX "L" COUNT OF LAST 10 DATA	4
2	L	-1	MAX "L" COUNT OF LAST 10 DATA AND SUM THE VALUES NEXT TO THEM	-32
3	L	-1	MAX "L" COUNT OF ALL DATA	5
4	L	-2	MAX "L" COUNT OF ALL DATA AND SUM THE VALUES NEXT TO THEM	-7
5	L	-2
6	W	4
7	L	-5
8	L	-10
9	W	3
10	W	4
11	W	5
12	L	-6
13	L	-8
14	L	-2
15	L	-16
16	W	6
17	W	8
18	L	-9
19	W	8
20	W	12
21	#N/A	#DIV/0!
22	#N/A	#DIV/0!
23	#N/A	#DIV/0!
24	#N/A	#DIV/0!

<tbody>
</tbody>

 

[mubashiraziz]

You can apply =iferror(formula,"")

 

[Josephoo]

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.

 

[Domenic]

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

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

L	-1		10	4	-32
L	-1			5	-7
L	-1
L	-2
L	-2
W	4
L	-5
L	-10
W	3
W	4
W	5
L	-6
L	-8
L	-2
L	-16
W	6
W	8
L	-9
W	8
W	12
#N/A	#DIV/0!
#N/A	#DIV/0!
#N/A	#DIV/0!
#N/A	#DIV/0!

<tbody>
</tbody>

Hope this helps!

 

[Josephoo]

After much solving. I manage to get it to work!! thanks heaps!! Kudos to ya!!! i have learnt alot!

 

[Domenic]

You're very welcome. And thanks for the feedback.

Cheers!