Opzoeken met somproduct waarvan de uitkomst gemiddeld wordt

[Rizo83]

Ik heb een excelfile waar ik van een aantal Subjecten (kolom B) een uitkomstvariabelen (Kolom E) bij houd per datum (Kolom C).

Nu wil ik per subject (in kolom H) per jaar (kolommen J, K & L) het gemiddelde per jaar uitrekenen. Ik heb netjes met somproduct een aantal cellen die voldoen aan de voorwaarde een uitkomst van 1 of meerdere cellen. Nu wil ik hier het gemiddelde van weergeven in de betreffende cel (J3:L9). Hiervan zijn een aantal ingevuld en ik heb de formule die ik dacht dat zou werken ook ingevuld. Helaas komt hier #Waarde uit, ...

Ik heb het bestand ter verduidelijking toegevoegd.

Alvast dank voor de hulp!

 

[VenA]

Waarom niet met een draaitabel?

 

[Rizo83]

Dank, alleen moet het ook kunnen met een formule toch?

 

[MarcelBeug]

In jouw voorbeeldbestand komen geen #WAARDE fouten voor ...
Het probleem met de oorspronkelijke formule is dat je alleen met relatieve celverwijzingen werkt, waardoor deze niet meer kloppen als je de formule doortrekt.

Dus niet zo

=gemiddelde(sompRODUCT((B2:B17=H4)*(C2:C17>J1)*(C2:C17<K1)*(E2:E17))

Maar zo (in J4 en gekopieerd naar het hele bereik J3:L9):

=GEMIDDELDE(SOMPRODUCT(($B$2:$B$17=$H4)*($C$2:$C$17>=J$1)*($C$2:$C$17<K$1)*($E$2:$E$17)))

Let ook op het subtiele verschil tussen >J1 en >=J$1, zodat 1 januari niet tussen wal en schip valt.
Ik heb ook 1-1-2017 toegevoegd in M1.

 

[Rizo83]

Raar, ... in het originele bestand komen dus wel die #Waarde! voor. Het principe zou hetzelfde moeten zijn ...

MAar dank, de formule klopt in elk geval nu! Daarmee ben ik al geholpen, ik zal nu moeten kijken waarom het niet wil met een ander bestand...

ThnX!

 

[Rizo83]

Het was mij gister niet direct opgevallen, maar de volledige vraag is nog niet opgelost met de oplossing van MarcelBeug:

@Marcel: In de excelfile staan niet de gemiddelde maar de som van alle getallen. Zo staat in J3 het getal 26, wat de som is van 12 (E2) en 14(E8). Ik zou graag hier 13 willen hebben staan ipv 26. Dit lukt dus niet met de formule gemiddelde.

Heeft iemand een suggestie voor een oplossing?

 

[Rizo83]

De oplossing is simpeler dan ik dacht, namelijk:

delen door hetzelfde somproduct, zonder de laatste kolom. Hier komt namelijk het aantal cellen uit die aan de voorwaarde voldoet.

Groet,

Rizo

 

[snb]

=iferror(sumifs(e2:e17;b2:b17;h3;c2:c17;">=" & j$1;c2:c17;"<" & k$1)/countifs(b2:b17;h3;c2:c17;">=" & j$1;c2:c17;"<" & k$1);0)