combinatie SOMPRODUCT fromule, en gemiddelde berekening

[Flightcase]

beste,
ik zoek een manier om met een somproductformule een bepaalde gemiddelde berekening te maken van een uitlezing. Echter gaat dit ver mijn petje te boven. In het voorbeeld laat ik even de voorwaarden van deze berekening zien + een cijfermatig voorbeeld.

graag een handje hulp om dit even te realiseren aub?
alvast heel erg bedankt!

laat mij aub weten als er onduidelijkheden in zitten.
ik sta U graag te woord. Bekijk bijlage debiet - kopie.xlsx

 

[rebmog]

Ik hoop dat ik de vraagstelling goed begrepen heb. Hierbij een oplossing met hulpkolom en matrixformule.

Rebmog

 

[Flightcase]

Dag Rebmog,

Ik denk wel dat dit wel eens een juiste benadering zou kunnen zijn. Als ik de formule goed begrepen heb gaat het de gemiddelden berekenen enkel wanneer alle ALS voorwaarden vervult zijn? Dan zit uw formule goed denk ik. Moet ik met excel 2010 deze matrix formules ook activeren door Ctrl- shift-enter te toetsen?

Heeft iemand enig idee hoe ik deze formule in VBA mag schrijven?

Alvast bedankt Rebmog, ik ga deze eens loslaten op enkele sheets en zien wat het resultaat daarvan is.

 

[rebmog]

Ook in Excel 2010 moeten matrix-formules met CTRL+SHIFT+ENTER afgesloten worden. Met VBA kan deze matrixformule als volgt ingesteld worden:

Range("I18").FormulaArray = "=AVERAGE(IF(R3C5:R838C5=1,IF(R3C4:R838C4=0,IF(R3C3:R838C3>1,R3C3:R838C3))))"

Zoals te zien is moet in VBA bij matrix-formules R1C1-verwijzingen gebruikt worden i.p.v. A1-verwijzingen.

Rebmog

 

[cow18]

zonder matrixformule

=GEMIDDELDEN.ALS(C3:C1054;D3:D1054;"0";C3:C1054;">1";E3:E1054;"1")

 

[E v R]

Nog eentje:

wel matrix maar zonder hulpkolom:

=GEMIDDELDE(ALS(B4:B839<>B3:B838;ALS(D4:D839=0;ALS(C4:C839>1;C4:C839))))

Daarnaast een iets andere uitkomst..
wellicht zit ik er naast maar het lijkt mij dat bijv. rij 310 wel meegeteld moet worden


Met VBA een simpel loopje om te demonstreren:

Sub tst()
    For i = 4 To 838
        If Cells(i, 2).Value <> Cells(i - 1, 2).Value Then
            If Cells(i, 3).Value >= 1 Then
                If Cells(i, 4).Value = 0 Then
                    x = x + Cells(i, 3)
                    y = y + 1
                End If
            End If
        End If
    Next i
    MsgBox " som= " & x & " aantal= " & y & " gemm.= " & x / y
End Sub

 

[Thoralf]

Gegeven de voorwaarden in jouw voorbeeldbestand (waarbij je niet alle 1015 regels vult), kom ik weliswaar tot een andere uitkomst, maar dat wordt wellicht veroorzaakt doordat er voor de berekening van het gemiddelde van een onjuiste rekenmethode wordt uitgegaan: alle waarnemingen worden geteld, terwijl voor de sommering slechts een deel van de waarnemingen worden meegenomen, waardoor het gemiddelde te laag uitvalt.
Ik kom op een gemiddelde volgens de opgave in het voorbeeldbestand van 27,77197 met de volgende matrix-formule:
=GEMIDDELDE(ALS((B4:B1014<>B5:B1015)*(D5: D1015=0)*(C5:C1015>=1)=0;"";C5:C1015))

 

[Flightcase]

amai, heel erg bedankt voor de talrijke reacties! Ik ben zeer aangenaam verrast . Ik zou eens een analytische test moeten doen in 1 voorbeeld om de sterkst benaderende resultaat te vergelijken , maar ook dit is geen evidentie. Ik kan komend weekend dit eens grondig onder de loep nemen. Jullie zijn alvast heel erg bedankt voor de ideeën! SUPER :thumb: