Totaal verlies tijdens maximale losing streak berekenen

[Revolutionary]

Goedemiddag helpers,

Gister heb ik in het Excel forum een vraag geplaatst over het berekenen van de maximale winning en losing streak, zie: http://www.helpmij.nl/forum/showthread.php/576593-Maximale-winning-streak-berekenen

Nu zou ik ook graag het totale verlies tijdens deze maximale losing streak willen berekenen. Ik heb daarom geprobeerd om de formule die de maximale losing streak uitrekent uit te breiden met een SOM-functie. Dat ziet er dan als volgt uit:

=SOM(MAX(TRANSPONEREN(INTERVAL(ALS(C1:CX1<0;KOLOM(C1:CX1));ALS(C1:CX1>=0;KOLOM(C1:CX1))))))

Ik heb ook een aantal varianten geprobeerd, zoals de functies MAX en KOLOM verwijderen, maar de oplossing is toch iets lastiger dan ik had gehoopt...

Ik heb ook een bijlage toegevoegd: Bekijk bijlage Voorbeeld 2.xlsx
De berekeningen staan op het 2e tabblad.

Wie kan mij helpen?

Alvast bedankt!

Tim

 

[Revolutionary]

Heeft iemand een idee hoe ik de formule moet aanpassen? De formule telt nu het totaal aantal cellen dat een negatieve waarde heeft, dus de SOM-functie in mijn formule lijkt niet te werken.

Tim

 

[E v R]

Je kunt meerdere net zulke grote streaks hebben, van welke wil je dan de som hebben?
Ik heb een 'simpel' UDF-je gemaakt die beide formules max en som aan zou moeten kunnen. Het grootste verlies (of winst) bij de grootste streak wordt aangegeven.
plaats onderstaand UDF-je in een module:

Function SumMaxStreak(rng As Range, Win As Boolean, Optional Streak As Boolean)
    Dim cel As Range, i As Long, j As Double, y As Long, z As Double
    If Win = False Then
        For Each cel In rng
            If cel.Value < 0 Then
                i = i + 1
                j = j + cel.Value
                If i = y And z < j Then z = j
                If i > y Then z = j
                y = Application.WorksheetFunction.Max(y, i)
            Else
                i = 0
                j = 0
            End If
        Next cel
    Else
        For Each cel In rng
            If cel.Value > 0 Then
                i = i + 1
                j = j + cel.Value
                If i = y And z < j Then z = j
                If i > y Then z = j
                y = Application.WorksheetFunction.Max(y, i)
            Else
                i = 0
                j = 0
            End If
        Next cel
    End If
    If Streak = True Then
        SumMaxStreak = y
    Else
        SumMaxStreak = z
    End If
End Function

Roep deze functie als volgt aan: (formule in jouw sheet)
Voor max streak verlies: =SumMaxStreak(A2:J2;ONWAAR;WAAR)
Voor max streak win: =SumMaxStreak(A2:J2;WAAR;WAAR)
Voor som max streak verlies: =SumMaxStreak(A2:J2;ONWAAR)
Voor som max streak winst: =SumMaxStreak(A2:J2;WAAR)

je moet wel macro's accepteren en
bij engelse versie de WAAR en ONWAAR vervangen door TRUE en FALSE

 

[Superzeeuw]

@ EVR,

Ik zie geen verschil in de groene en rode code?

Function SumMaxStreak(rng As Range, Win As Boolean, Optional Streak As Boolean)
    Dim cel As Range, i As Long, j As Double, y As Long, z As Double
    If Win = False Then
        For Each cel In rng
            If cel.Value < 0 Then
               [COLOR="Lime"] i = i + 1
                j = j + cel.Value
                If i = y And z < j Then z = j
                If i > y Then z = j
                y = Application.WorksheetFunction.Max(y, i)
            Else
                i = 0
                j = 0[/COLOR]
            End If
        Next cel
    Else
        For Each cel In rng
            If cel.Value > 0 Then
               [COLOR="Red"] i = i + 1
                j = j + cel.Value
                If i = y And z < j Then z = j
                If i > y Then z = j
                y = Application.WorksheetFunction.Max(y, i)
            Else
                i = 0
                j = 0[/COLOR]
            End If
        Next cel
    End If
    If Streak = True Then
        SumMaxStreak = y
    Else
        SumMaxStreak = z
    End If
End Function

Volgens mij kan de code nog eenvoudiger met 1 If ipv 2

If cel.Value > 0 Then
                i = i + 1
                j = j + cel.Value
                If i = y And z < j Or i>y Then z = j
                y = Application.WorksheetFunction.Max(y, i)
            Else
                i = 0
                j = 0
            End If

Maar ik kan het mis hebben.

 

[E v R]

de groene en rode code zijn inderdaad exact gelijk, maar de eerste telt alle negatieve waarden indien Win = false en de tweede als Win = niet false. < 0 versus > 0

De TS vroeg inderdaad enkel alleen om de eerste, maar ik verwachtte dat de som van de max winning streak dan wel zou volgen, vandaar de tweede for each die anders toch niet aangeroepen zou worden.

Nu kan dit zeker in dezelfde loop worden opgelost maar of de code daar efficienter/sneller van wordt lijkt me sterk.

Zonder loop en tellertjes zie ik overigens niet hoe het anders aangepakt zou moeten worden en met gewone (matrix)formules leek het mij haast onbegonnen werk.

++

die 2 if regels achter elkaar zitten er bewust in, indien de TS niet de hoogste som bij gelijke max maar de eerste som van de grootste streak zou willen zou hij enkel die eerste regels moeten remarken.

 

[Superzeeuw]

@ EVR,

Ik jouw opmerkingen eens doorgenomen en heb nog eens aandachtig naar het programma gekeken en zie nu wat je gemaakt hebt.
Je code ziet er voorts goed uit en denk ook niet dat het sneller kan.
Ik begrijp nu waarom je een dubbele IF hebt staan.

 

[Superzeeuw]

@ EVR,

Ik kon het niet met rust laten.
Heb nog wat zitten stoeien en kwam tot het volgende:

Function SumMaxStreak(rng As Range, Win As Boolean, Optional Streak As Boolean)
    Dim cel As Range, i As Long, j As Double, y As Long, z As Double
    If Win = False Then
        y = Application.WorksheetFunction.Max(rng.Cells.Value)
    Else
        y = Application.WorksheetFunction.Min(rng.Cells.Value)
    End If
    If Streak = True Then
        SumMaxStreak = y
    Else
        SumMaxStreak = Application.WorksheetFunction.Sum(rng.Cells.Value)
    End If
End Function

Hoe en wat met WAAR en ONWAAR kun jij wel bepalen maar volgens mij komt dit in de buurt, of heb ik het mis.

 

[cow18]

volgens mij hebben jullie gewoon de min, max en totaal winst/verlies berekend van de ganse reeks en niet die in de langste winst of verlies streak, dit is de langst ononderbroken winst of verlies reeks

 

[cow18]

in bijlage even die 4 zaken uitgerekend via UDF. Als je dat voor een paar duizend rijen wil doen, dan gebruikje beter dat in een echte macro ipv bv; 4 UDF's per rij
Nu zou je eigenlijk voor iedere rij nog een aantal extra zaken tegelijkertijd kunnen uitrekenen.

 

[E v R]

volgens mij hebben jullie gewoon de min, max en totaal winst/verlies berekend van de ganse reeks en niet die in de langste winst of verlies streak, dit is de langst ononderbroken winst of verlies reeks

De code uit post #3 doet dit dus juist wel, de aangepaste code van Superzeeuw pakt inderdaad alles over de ganse reeks.

Ik heb mijn functie ook nog even in het bestandje gezet, een verschil in uitkomst kun je hebben bij de som van de langste pos streak omdat cow18's functie de som neemt van de eerste grootste streak en ik van de grootste som van de grootste streak.

Als je dit inderdaad voor een paar duizend rijen wilt doen zou ik alles in een macro stoppen, dus ook de uitkomst van de formules in kolom A t/m J

 

[Revolutionary]

E v R, Superzeeuw & cow18 heel erg bedankt voor jullie hulp!:thumb:

Ik zie dat jullie druk bezig zijn geweest, met het nodige resultaat Ik ben gister en vandaag niet thuis geweest, dus ik kon niet eerder reageren.

@ E v R: ik had inderdaad niet stil gestaan bij het feit dat je meerdere even lange streaks kan hebben, het is dan inderdaad de bedoeling dat de formule de waarde aangeeft van het grootste verlies. Ik zie ook dat je een dergelijk formule hebt opgesteld, goed man!

Morgen vroeg ga ik jullie codes gelijk testen, ik hou jullie op de hoogte!

Ander vraagje, kunnen jullie een goed VBA boek voor beginners aanraden? Ik heb thuis VBA voor dummies, maar daar heb ik tot nu toe weinig aan gehad... Ik zoek eigenlijk een VBA boek die uitlegt hoe ik formules kan opstellen zoals jullie ze in dit topic gepost hebben.

Fijne avond!

Tim

 

[Revolutionary]

Heren, ik heb jullie codes getest en die van E v R doet precies wat ie moet doen, namelijk de grootste som berekenen van de grootste streak. Dus die code ga ik gebruiken

Nogmaals heel erg bedankt voor jullie hulp:thumb:

En als jullie nog een goed boek weten over VBA dan hoor ik het graag.
Ik zet deze topic op opgelost.

Groeten,
Tim

 

[Revolutionary]

Ik heb deze topic weer op onopgelost gezet omdat ik toch nog een vraagje heb.

Als ik de code van E v R toepas op een horizontale reeks met data dat werkt alles prima, pas ik de formule echter toe op een verticale reeks met data dan geeft de formule niet langer de grootste som van de grootste streak, maar geeft de formule de som van de eerste grootste streak.

Mijn wens was inderdaad om de formule toe te passen op horizontale reeksen, aangezien ik straks 10.000 rijen heb. Maar in het gehele bestand komt ook 1 kolom te staan met een verticale reeks met data. Ik vermoed dat de vba-code zo is opgesteld dat hij wel werkt op horizontale reeksen, maar niet op verticale reeksen. Iemand een idee wat ik moet aanpassen? De formule voor de horizontale en verticale reeksen hoeven niet perse in 1 VBA code te staan, als het makkelijker is om 2 VBA codes te gebruiken is dat ook prima.

Alvast bedankt!

Tim

 

[E v R]

zolang de range betrekking heeft op 1 rij of 1 kolom maakt het mi niet uit en zou het moeten kloppen, de code loopt gewoon heel simpel cel voor cel af.

Plaats anders eens een voorbeeldje waar het niet in werkt.

 

[Revolutionary]

Hierbij een voorbeeldje E v R. Bekijk bijlage Voorbeeld 3.xls

Ik ben zometeen weg, dus ik zal waarschijnlijk morgen pas weer reageren.

Mvg,
Tim

 

[E v R]

pas het haakje aan:

Function SumMaxStreak(rng As Range, Win As Boolean, Optional Streak As Boolean)
    Dim cel As Range, i As Long, j As Double, y As Long, z As Double
    If Win = False Then
        For Each cel In rng
            If cel.Value < 0 Then
                i = i + 1
                j = j + cel.Value
                If i = y And z [COLOR="red"][B]>[/B][/COLOR] j Then z = j
                If i > y Then z = j
                y = Application.WorksheetFunction.Max(y, i)
            Else
                i = 0
                j = 0
            End If
        Next cel
    Else
etcetera

nu = -3.2 groter dan -4.02

 

[Revolutionary]

Thanks alot E v R!

Deze topic gaat wederom op opgelost.