Gemiddelde net even anders

[Colombe]

Ik heb reeksen gegevens per jaar geordend. Ieder jaar voeg ik weer een regel toe voor het lopende jaar. Ik wil graag weten wat het totaal is en ook wat het gemiddelde. Tot zover simpel. Maar wat ik ook zou willen zien is het gemiddelde over alle jaren zonder het laatste jaar in de kolom. Ofwel omdat het laatste jaar sterk afwijkt van het gemiddelde, zoals in het bijgevoegde voorbeeld. Ofwel omdat in het lopende jaar de gegevens nog niet het gehele jaar betreffen en daardoor het gemiddelde sterk beïnvloeden.
Wie helpt me aan de juiste formule?

 

[Cobbe]

En als je nu eens volgende formule gebruikt:

=GEMIDDELDE($D$7:$D7)

in E7 en verder naar beneden trekken.

 

[troedeboer]

De volgende formule pakt de range vanaf rij 7 en dan het aantal -1 waardes naar beneden, en daar weer het gemiddelde van

=GEMIDDELDE(VERSCHUIVING(D:D;6;0;AANTAL(D7:D18)-1))

 

[Colombe]

Hartelijk dank Cobbe en Troedeboer.
Helaas werken beide formules niet goed. Het is makkelijk te controleren door een regel voor jaar 2012 in te voegen.
Nog andere suggesties?

 

[troedeboer]

Wat klopt er niet aan?
Ik heb 2012 ingevuld en hier 1000 neer gezet.
Nu geeft het gemiddelde aan (ex laatste jaar) 1985,77
dit is precies het totaal van 2001 tot 2011 gedeeld door 11.
Bekijk bijlage Gemiddelde.xls

 

[Colombe]

Excuses Troedeboer, ik had kennelijk iets verkeerd gedaan. Nu werkt het wél. Inmiddels heb ik zelf ook al een oplossing gevonden, die ook werkt:
=(SOM(D720)-VERT.ZOEKEN(MAX(C7:C20);C720;2))/(AANTAL(D720)-1)

edit: nb sorry voor de smileys, moet zijn ":"

 

[Colombe]

Er gaat kennelijk toch ergens iets fout, troedeboer. Nu werkt jouw formule weer niet bij mij. Kijk maar in het bestandje.

edit: nu ik mijn formules gebruik in het definitieve model, gaat er ook iets mis, dus mijn formule klopt ook nog niet helemaal.

 

[troedeboer]

Dit komt door de verschuiving.
In het eerste voorbeeldbestand heb je de waardes laten beginnen op rij 7
Nu laat je de waardes op rij 6 starten.
Hierdoor rekent mijn formule vanaf een regel te laag.
Je zou de formule nog kunnen wijzigen zodat deze zelf de eerste waarde in de kolom opzoekt.

 

[Colombe]

Het is me duidelijk, troedeboer. Ik herinner me niet er even een rijtje te hebben tussengezet. Mijn formule klopt nu trouwens ook in het model, ik had daar weer een klein foutje gemaakt.