min max and sum forluma assistance

[pietf]

need a hand with formula resulting with;
- max win streak & corresponding accumulated total
- max loss streak & corresponding accumulated total
as far a i have tested the positive streak calc's work (see cells in blue of attachment), but the negative streak calc's does not.. i am sure i am missing something

attached a sample worksheet
column details;
A - date in number format
B - user input data
D - accumulated total of B
F - calc win, loss or draw from B
G - helper to carry win or loss if draw is found
H - streak calc
I - helper for indexing
J - sum of value (D) per streak
((I & J - thanks to help from forum))

[Bernie Deitrick]

If you change your streak formula to negative numbers for losing streaks, it greatly simplifies the formulas to find the max streaks and sums of streaks.

My suggestion for doing that is, for cell G5 (after getting rid of the other helper columns):

=IF(F5*G4<0,F5,G4+F5)

Copied down - and the other formulas are in an example workbook, attached.

[pietf]

thank you.. will have a look & revert

[pietf]

change to column G works perfect.. thank you

streak sum may be due to my typing..
this should be sum of value for the max streak & min streak respectively
with my knowledge replacing last 2 helper rows & using index/match i can get
win streak value; but loss streak results in error due to multiple occurances
i added expected result in red

[Bernie Deitrick]

Do you actually want all the point from the streak - the cells in yellow in this file? OR do you want to ignore the first value of each of the streaks? And what do you want if you have two streaks of the same length?

Sample Book 3 B.xlsx

[pietf]

first value to be ignored as it is where the streak starts (i.e. zero in normal counting terms)
when more than one streak has same value/length the highest/longest sum should be returned
eg. there are two -4 streaks resulting in -87 and -108. the -108 is thus the max streak on loss side

thank you for all the help thus far

[Bernie Deitrick]

That needs a helper column of formulas:

Sample Book 3 C.xlsx

I should note that if the max or min sum is not associated with the longest streak, it will still be reported as the Max: if you have one streak with 6 and a sum of 150 and another streak that is 7, but with 140, then the 150 will be reported. Otherwise, you need to use array formulas - Entered using Ctrl-Shift-Enter

=MAX(IF(G:G=J5,H:H))

and

=MIN(IF(G:G=J8,H:H))

Not sure which you actually wanted.

[pietf]

thank you works perfectly