Summing Largest Consecutive Dollars and Consecutive Average Question

[MXL]

Hello,

I would like to know how i could sum the most consecutive wins in dollars in column A, and the average consecutive wins in column B.

In the table below, you can see in column A1:A3, three consecutive dollar wins which should total $43. How do i go about calculating the largest sum in dollars of consecutive wins which should be $43 because in column A5:A6, there are two consecutive wins that total only $12.

In column B1:B3, three consecutive wins which total 3 and in column B5:B6, two consecutive wins which total 2, how do i go about calculating the average consecutive wins in column B?

	A	B
1	$23	Win
2	$10	Win
3	$10	Win
4	-$42	Loss
5	$9	Win
6	$3	Win
7	-$15	Loss

<tbody>
</tbody>

Thank you for taking the time to read this, much appreciated.

 

[Andrew Fergus]

Hello

To do this using formulas you will need a couple of helper columns. Please see the image below.

Please note that array formulas must be entered as shown, but before you press Enter or confirm the formula, you need to press the Ctrl, Alt and Enter keys instead - you will know you have done this correctly when curly brackets {} automatically appear around the formula.

Please note this assumes there is a header row and the data starts in row 2. The formulas in columns C and D must be copied down as far as your data goes.

I trust this helps.

Value	Win/Loss	Consec	Consec Value		Average Consec Wins	Longest Consec Wins	Longest Consec $	Largest Consec $
$23.00	Win				2.5	4	$42.00	$43.00
$10.00	Win				=AVERAGE(C:C)
$10.00	Win	3	$43.00			=MAX(C:C)
-$42.00	Loss						=INDEX(D:D,MATCH(G2,C:C,0))
$9.00	Win							=MAX(D:D)
$3.00	Win	2	$12.00
-$15.00	Loss
-$10.00	Loss
-$5.00	Loss
$5.00	Win
$10.00	Win
$12.00	Win
$15.00	Win	4	$42.00
-$5.00	Loss
$12.00	Win	1	$12.00
Array Formula in C2: (copy down)
	=IF(A2>0,IF(A3>0,"",ROW(A2)-MAX(IF(B$1:B2<>"Win",ROW(B$1:B2)))),"")
Array Formula in D2: (copy down)
	=IF(ISNUMBER(C2),SUM(IF(ROW(A$1:A2)>(ROW(A2)-C2),A$1:A2)),"")

<tbody>
</tbody>

 

[MXL]

Thank you so much Andrew, much appreciated.

 

[Andrew Fergus]

Just saw a typo - it's not Ctrl+Alt+Enter to confirm the formula, it is actually Ctrl+Shift+Enter. Once you have entered the array formula into C2 but before you press Enter, instead press Ctrl (hold it) then Shift (also hold that) and then press Enter (and release all 3 at the same time). You will know it has worked when curly brackets {} appear around the formula.

Edit: and repeat for the formula in D2.

 

[Aladin Akyurek]

@ Andrew Fergus

Longest consecutive $ would require MAXIFS or array-processing:

=MAXIFS(D:D,C:C,G2)

{=MAX(IF(C:C=G2,D:D))}

where G2 = Longest Consec Wins.

 

[Andrew Fergus]

Hi Aladain
Thanks for that. I hadn't stipulated G2 contained the number of Longest Consecutive Wins. I noticed all 3 methods return the same value. Why do you not recommend the Index/Match approach? (Am I correct in assuming you are not recommending this?)
Andrew

 

[Aladin Akyurek]

Hi Aladain
Thanks for that. I hadn't stipulated G2 contained the number of Longest Consecutive Wins. I noticed all 3 methods return the same value. Why do you not recommend the Index/Match approach? (Am I correct in assuming you are not recommending this?)
Andrew

Row\Col	A​	B​	C​	D​
1​
2​	$23 ​	Win​
3​	$10 ​	Win​
4​	$10 ​	Win​	3​	43​
5​	($42)​	Loss​
6​	$9 ​	Win​
7​	$3 ​	Win​
8​	$100 ​	win​	3​	112​
9​	($15)​	Loss​
10​

Hi Andrew,

Longest consec $ should be 112 dollars. Index+Match can miss that.

Aladin

 

[Andrew Fergus]

Nice catch Aladin.