formule voor gemiddelde

[hans1974]

Weet er iemand een formule om in de eerste kolom het gemiddelde te berekenen van de getallen > 100 die achtereenvolgend zijn, daarna telkens de lage waarde ervoor en de lage waarde erna mee te rekenen.
zie vb. daar staat de uitleg bij het voorbeeld
Alvast bedankt voor mee te helpen

Bekijk bijlage 2013.xlsm

 

[Cobbe]

Had je dit voor ogen?

 

[hans1974]

hey Cobbe,

bijna, hij moet de stukken apart nemen niet van het geheel het gemiddelde nemen. (dus overal waar 1 staat een berekening maken van het stuk in de tabel dat 1 is en zo verder naar beneden. Misschien met index formule?....

 

[cow18]

zie bijlage met een aantal rijen geschrapt zodat bestand < 100 kB

 

[hans1974]

goed gevonden cow, daar kan ik verder mee.
Bedankt,

@Cobbe, als je nog een manier weet mag je me dit altijd laten weten, kan ik altijd verder van leren

Bedankt

 

[jolivanes]

Quote: @Cobbe, als je nog een manier weet mag je me dit altijd laten weten, kan ik altijd verder van leren

Ik ben Cobbe niet maar hopelijk vind je het niet erg dat ik ook een idee heb (zie bijlage)

 

[jolivanes]

Als je het gemiddelde van de eerste (A) kolom ook moet hebben zou je de code alsdus kunnen veranderen:

Sub hans()
    Dim ws As Worksheet
    Dim rng As Range, r As Range, s As Range
    Dim b As Long, c As Long
    Set ws = Worksheets("Sheet1")
    Set rng = ws.Range("C7")
    
    While rng.Value <> ""
    
        If rng.Value = 0 Then
            b = 1 And c = 0
        Else
            b = 0 And c = 1
        End If
        
        With ws.Range(rng.Address & ":C" & Cells(Rows.Count, 3).End(xlUp).Row)
            Set r = .Find(What:=b, After:=rng, LookIn:=xlValues, LookAt:=xlWhole)
            On Error GoTo ErrorHandler
            Set s = .Find(What:=c, After:=r, LookIn:=xlValues, LookAt:=xlWhole)
        
        s.Offset(, 2).Formula = "=Average(" & r.Offset(-1, -1).Address & ":" & s.Offset(, -1).Address & ")"
        s.Offset(, 6).Formula = "=Average(" & r.Offset(-1, -2).Address & ":" & s.Offset(, -2).Address & ")"
        's.Offset(, 2).Value = "Average " & Format(s.Offset(, 2).Value, "##.##")    '<---- Kan zijn dat dit "##,##" moet zijn
        s.Offset(, 2).Value = "Average " & r.Offset(-1, -1).Address & ":" & s.Offset(, -1).Address & " = " & Format(s.Offset(, 2).Value, "##.##")
        s.Offset(, 6).Value = "Average " & r.Offset(-1, -2).Address & ":" & s.Offset(, -2).Address & " = " & Format(s.Offset(, 6).Value, "##.##")
        Set rng = s
        End With
    Wend
    
ErrorHandler:
    Exit Sub
End Sub

 

[cow18]

iets compacter

Sub Bart()
  Dim ar       As Range
  With Range("A5").CurrentRegion.Offset(, 3).Resize(, 1)   'gebruik de D-kolom naast je gegevens
    .FormulaR1C1 = "=if(RC1>100,1,"""")"                   'zet er deze formule in
    .Value = .Value                                        'vervang de formule door de waarden
    For Each ar In .SpecialCells(xlConstants).Areas        'kijk enkel naar blokken van de cellen waar er iets in staat
      ar.Offset(, 1).Resize(1, 3).Value = Array(WorksheetFunction.Average(ar.Offset(, -3)), WorksheetFunction.Average(ar.Offset(, -2)), WorksheetFunction.Sum(ar))  'zet in de 1e rij ernaast het gemiddelde van de 1e en de 2e kolom en het aantal
    Next
  End With
End Sub

 

[jolivanes]

Goeie morgen Bart.
U bent vroeg uit de veren!

Als ik het goed begrijp wil Hans ook de waarde in de kolommen voor de eerste een (1) alsmede de waarde erna bijgerekend hebben.
Zo ziet het er in de bijlage naar uit in ieder geval maar dat is niet zo duidelijk in zijn antwoord op Cobbe's eerste antwoord.
We horen het wel van hem verwacht ik.

Groetjes.
Han

 

[cow18]

Sub Bart()
  Dim ar As Range, ar1 As Range
  With Range("A5").CurrentRegion.Offset(, 3).Resize(, 1)   'gebruik de D-kolom naast je gegevens
    .FormulaR1C1 = "=if(RC1>100,1,"""")"                   'zet er deze formule in
    .Value = .Value                                        'vervang de formule door de waarden
    For Each ar In .SpecialCells(xlConstants).Areas        'kijk enkel naar blokken van de cellen waar er iets in staat
      Set ar1 = ar.Offset(-1).Resize(ar.Cells.Count + 2)   'voeg de cel ervoor en erna toe
      ar.Offset(, 1).Resize(1, 4).Value = Array(WorksheetFunction.Average(ar1.Offset(, -3)), WorksheetFunction.Average(ar1.Offset(, -2)), WorksheetFunction.CountA(ar1.Offset(, -2)), ar1.Offset(, -3).Address)  'zet in de 1e rij ernaast het gemiddelde van de 1e en de 2e kolom en het aantal
    Next
  End With
End Sub

 

[jolivanes]

Heel wat korter (compacter) Bart.
Mooi.

 

[hans1974]

knap bedankt hiervoor mannen, beide kan ik goed gebruiken!

@Bart, dat is perfect, ik heb de gemiddelde waarde van hetgeen ik moet hebben en het aantal waarvan je het gemiddelde neemt, daarmee kan ik dan verder rekenen.

 

[jolivanes]

@ Hans & Bart
Wat dom van mij. Ik zie nu pas dat kolom C een help kolom is.
Mijn excuses daarvoor.

 

[hans1974]

Hey Jolivanes, geen excuses nodig. Jij bedankt voor de hulp!!