Gemiddelde berekenen van X aantal getallen

[DEWCAP]

Hallo,

In een excel file heb ik 2 kolomen nl StartDate en EndDate. Hiervan zou ik graag het verschil (Duration) berekenen en nadien een gemiddelde maken van alle Durations.

Ik weet wel hoe ik dit manueel moet aanpakken maar ik zou dit graag automatiseren. Aangezien de kolomen niet altijd hetzelfde aantal rijen hebben, is dit niet evident voor mij.

Bijkomend probleem: Als de StartDate en EndDate dezelfde zijn, geeft excel de waarde 0, terwijl dit eigenlijk 1 moet zijn.

Alvast bedankt voor de hulp.

DEWCAP

 

[Paul Groote S]

Hoi DEWCAP

Ik heb wat geprobeerd, ik weet niet of dit is wat je bedoelt en het kan misschien ook wel handiger want ik ben nog maar 2 weken bekent met VBA. Maar voor mij werkt het.

Gr Paul

 

[Finch]

Hoi DEWCAP

Ik heb wat geprobeerd, ik weet niet of dit is wat je bedoelt en het kan misschien ook wel handiger want ik ben nog maar 2 weken bekent met VBA. Maar voor mij werkt het.

Gr Paul

Voor nog maar 2 weken met VBA bezig te zijn is dat helemaal niet slecht :thumb:

 

[DEWCAP]

Hello Paul,

Dit zit er goed uit! Heb 1 opmerking. De formule om het aantal dagen te bereken telt ook de weekend erbij. Ik weet dat je met de fx NETWORKDAYS dit kan oplossen maar kan dit natuurlijk niet in VBA verwerken.

Ik post even je VBA zodat anderen gemakkelijker kunnen meedenken.

Sub datums()
Dim Rng1 As Date
Dim Rng2 As Date



Range("A2").Select
Do Until Selection = Empty
Rng1 = Selection
Selection.Offset(0, 1).Select
Rng2 = Selection
Selection.Offset(0, 1).Select
Selection.Value = Rng2 - Rng1
Selection.Offset(1, -2).Select
Loop
Range("D2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(C[-1])"

End Sub

Alvast hartelijk bedankt. Voor nog maar 2 weken met VBA te werken vind ik dit al héél goed!

 

[DEWCAP]

Ik merk nu pas een bijkomend probleem. Als StartDate = EndDate is het resultaat 0.
Daardoor geeft het gemiddelde geen juiste weergave van de realiteit. Dit kan je ook voorkomen met NETWORKDAYS, alleen weet ik niet hoe dit in onze VBA te plakken...

 

[Finch]

Hello Paul,

Dit zit er goed uit! Heb 1 opmerking. De formule om het aantal dagen te bereken telt ook de weekend erbij. Ik weet dat je met de fx NETWORKDAYS dit kan oplossen maar kan dit natuurlijk niet in VBA verwerken.

Ik post even je VBA zodat anderen gemakkelijker kunnen meedenken.



Alvast hartelijk bedankt. Voor nog maar 2 weken met VBA te werken vind ik dit al héél goed!

Dan die je deze aanpassing te maken:

Selection.Value = Rng2 - Rng1

te vervangen door:

Selection.FormulaR1C1 = "=NETWORKDAYS(RC[-2],RC[-1])"

edit: Je kan indien nodig achter die formule nog +1 zetten. Dat ligt aan je definitie van duration. Vind je dat tussen 01/01/2000 en 02/01/2000 er 1 dag zit, dan is de bovenstaande correct. Vind je dat er 2 werdagen tussen zitten nl. 1 dag voor 01/01 en 1 dag voor 02/01 dat moet jer nog +1 achterzetten.

 

[DEWCAP]

Hallo Finch,

De oplossing die je geeft werkt ni.

Sub datums()
Dim Rng1 As Date
Dim Rng2 As Date



Range("A2").Select
Do Until Selection = Empty
Rng1 = Selection
Selection.Offset(0, 1).Select
Rng2 = Selection
Selection.Offset(0, 1).Select
Selection.FormulaR1C1 = "=NETWORKDAYS(RC[-2],RC[-1])"
Selection.Offset(1, -2).Select
Loop
Range("D2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(C[-1])"

End Sub

Het resultaat is in elk veld "#NAME?

 

[Finch]

Voor die functie moet wel Analysis ToolPak ingeschakeld zijn bij de add-ins. Is dit het geval?

 

[DEWCAP]

Ik heb het even op mijn andere pc geprobeerd en daar werkt het wel. Op deze pc heb ik excel 2000 en op de andere excel 2002, dat zal het verschil gemaakt hebben.

Beiden hartelijk bedankt!

Bijkomende vraag. Is het mogelijk om vooraleer het gemiddelde te bereken te verifieren of er in kolom B een datum staat? Wanneer er in kolom B geen datum staat, zou er dan automatisch de datum van kolom A moeten komen? Dit ook om te vermijden dat het gemiddelde te ver afwijkt.

Bedankt!

 

[DEWCAP]

Niemand?

 

[Finch]

Het kan allemaal wat korter, maar om de ingeslagen weg te volgen heb ik daarvoor even de oorspronkelijke code aangepast:

[FONT="Courier New"]Sub datums()
Dim Rng1 As Date
Dim Rng2 As Date
    
    Range("A2").Select
    Do Until Selection = Empty
    Rng1 = Selection
    Selection.Offset(0, 1).Select
    Rng2 = Selection
    If Rng2 = CDate(Empty) Then
        Selection = Rng1
    End If
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=NETWORKDAYS(RC[-2],RC[-1])"
    Selection.Offset(1, -2).Select
    Loop
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(C[-1])"

End Sub[/FONT]

 

[DEWCAP]

Deze code werkt niet. Ik krijg een "runtime error" 13 bij Rng2 = Selection.

 

[Finch]

Dat is raar want de wijziging aan de code is daarna pas gebeurt. Tot aan die regel is er niets veranderd.

edit: wat geeft deze code als resultaat?

[FONT="Courier New"]Sub datums()
Dim Rng1 As Date
Dim Rng2 As Date
    
    Range("A2").Select
    Do Until Selection = Empty
    Rng1 = CDate(Selection)
    Selection.Offset(0, 1).Select
    Rng2 = CDate(Selection)
    If Rng2 = CDate(Empty) Then
        Selection = Rng1
    End If
    Selection.Offset(0, 1).Select
    Selection.FormulaR1C1 = "=NETWORKDAYS(RC[-2],RC[-1])"
    Selection.Offset(1, -2).Select
    Loop
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(C[-1])"
End Sub[/FONT]

 

[DEWCAP]

Hi Finch,

Dezelfde error: Rng2 = CDate(Selection)

EndDate wordt in elke rij hetzelfde als de StartDate, wat niet de bedoeling is. De error krijg ik wanneer er geen EndDate is ingevuld.

 

[DEWCAP]

Kan iemand mij hiermee helpen aub?

 

[Finch]

Kan je jouw bestandje hier eens aanhangen. Dan kunnen we eens kijken waar het probleem zit, want die code werkt hier in het voorbeeldbestand van Paul.