Gemiddelden naar matrix/tabel

[Arno1980]

Goedemiddag,

Ik heb een vraagje die voor jullie misschien makkelijk op te lossen is. Ik heb een lijstje met maanden en bijbehorende tarieven, hiervan wil ik graag van alle mogelijke periodes in een matrix/tabel de gemiddelden weergeven.

Ter illustratie: voor januari t/m december weet ik van iedere maand een tarief. De periodes waarvoor ik de gemiddelde tarieven zoek zijn alle mogelijke combinaties, dus januari/februari (dus gemiddelde van 2 maanden), januari/maart (dus gemiddelde van 3 maanden) en andere combinaties van start en eindmaand.

Nog illustratiever: ik heb een voorbeeldbestandje toegevoegd, ik hoop dat mijn vraag hiermee nog duidelijker wordt.

Zouden jullie me aan de werkwijze kunnen helpen? Kan ik bijvoorbeeld misschien iets doen met draaitabellen, of is er een functie in Excel die matrices vormt?

Hartelijk dank alvast!!Bekijk bijlage Voorbeeld data naar gemiddelden in matrix_tabel.xlsx
Met vriendelijke groet,
Arno

 

[SjonR]

Met de Functie Gemiddelde lukt het uiteraard, maar je zal het wel allemaal handmatig moeten doen.

=GEMIDDELDE(D5:D6)

 

[Gijsbert1]

In I5:

=GEMIDDELDE(VERT.ZOEKEN($H19;$C$5:$D$16;2);VERT.ZOEKEN(I$18;$C$5:$D$16;2))

Dit doortrekken en.... whalaah..

 

[Arno1980]

Goedemiddag Gijsbert1,

Ik heb het vermoeden dat deze kan werken, maar in de formules voor verticaal zoeken verwijs je naar cellen in rijen 18 en 19. Die regels zijn in mijn voorbeeldbestandje leeg, kan het zijn dat je geschoven hebt met cellen, alvorens de formules op te zetten? Ik krijg nu een foutmelding in cel I5 waar de formule komt te staan.

Nogmaals, ik vermoed dat dit de oplossing kan zijn, zou je hier nogmaals naar willen kijken?

Alvast dank!

Met vriendelijke groet,
Arno

 

[AlexCEL]

Arno, kom op... dit kon je zelf ook oplossen toch, met een beetje logisch nadenken? Je had de oorzaak al te pakken!

Even de verwijzingen aanpassen levert:

=GEMIDDELDE(VERT.ZOEKEN($H5;$C$5:$D$16;2);VERT.ZOEKEN(I$4;$C$5:$D$16;2))

Succes ermee.

 

[Gijsbert1]

kan het zijn dat je geschoven hebt met cellen, alvorens de formules op te zetten?

Klopt!!!


Wat alexcel zegt

 

[Arno1980]

Ik had al een poging gedaan maar met foutmelding.. Zal een typfoutje zijn geweest dan :d (snel een excuus verzinnen: lange werkdag )

Anywho zo lijkt 'ie inderdaad te werken, thx!

Met vriendelijke groet,
Arno

 

[MarcelBeug]

Gents, dit gaat niet goed: die formule berekent telkens het gemiddelde van de eerste en de tweede maand. Niet van de eerste tot en met de tweede maand.
Ik kom op de volgende formule, met "" indien eindmaand < beginmaand:

=ALS(I$4<$H5;"";GEMIDDELDE(INDEX($D$5:$D$16;VERGELIJKEN($H5;$C$5:$C$16;0)):INDEX($D$5:$D$16;VERGELIJKEN(I$4;$C$5:$C$16;0))))

 

[Arno1980]

Ik was iets te snel denk ik, zo te zien pakt deze formule telkens het gemiddelde van alleen de startmaand en eindmaand, klopt dat? Ik moet eigenlijk ook de tussenliggende maanden meenemen in de berekening van het gemiddelde namelijk..

 

[MarcelBeug]

Zie post #8...

 

[Arno1980]

Ah de laatste reactie had ik niet gezien, lost het misschien al op.. ga ik proberen, dank!

 

[MarcelBeug]

Dit kan trouwens ook, al weet ik even niet vanaf welke Excel versie:

=ALS(I$4<$H5;"";GEMIDDELDEN.ALS($D$5:$D$16;$C$5:$C$16;">="&$H5;$C$5:$C$16;"<="&I$4))

 

[AlexCEL]

Ai, had niet inhoudelijk naar formule gekeken...

Mocht de functie GEMIDDELDEN.ALS niet werken, wellicht deze wel:

=ALS(I$4<$H5;"";SOMMEN.ALS($D$5:$D$16;$C$5:$C$16;">="&$H5;$C$5:$C$16;"<="&I$4)/(1+DATUMVERSCHIL($H5;I$4;"m")))

 

[MarcelBeug]

Mocht de functie GEMIDDELEN.ALS niet werken

Nee, die gaat het niet doen, met die ontbrekende D...

 

[AlexCEL]

Heel scherp... :thumb:

 

[Gijsbert1]

Gents, dit gaat niet goed: die formule berekent telkens het gemiddelde van de eerste en de tweede maand. Niet van de eerste tot en met de tweede maand

Klopt, dom van mij...

Misschien niet zo chique maar dit kan ook werken in I5:

=GEMIDDELDE(INDIRECT(ADRES(RIJ();4)):INDIRECT(ADRES(KOLOM()-3;4)))

 

[Arno1980]

Goedemorgen allen,

ik ben eerlijk gezegd een beetje het overzicht kwijt, het lijkt erop dat er meerdere werkende mogelijkheden tussen zitten. Probleem is nu dat ik geen variant werkend krijg in de daadwerkelijke (iets grotere) tabel.. zouden jullie me nog kunnen helpen hiermee?

In het bijgevoegde bestandje heb ik een overzicht meegestuurd met daarin de daadwerkelijke beschikbare maanden. De tarieven hierin zijn fictief (want gevoelig), die kan ik later omzetten naar de werkelijke tarieven. Zouden jullie me nog kunnen helpen om middels één van de genoemde formules, hierin de matrix met gemiddelde tarieven gevuld te krijgen?

In ieder geval hartelijk dank allen!
Bekijk bijlage Voorbeeld data naar gemiddelden in matrix_tabel v2.xlsx
Met vriendelijke groet,
Arno

 

[Gijsbert1]

Zolang de matrix niet verplaatst wordt blijft deze gewoon hanteerbaar:

in I5:

=GEMIDDELDE(INDIRECT(ADRES(RIJ();4)):INDIRECT(ADRES(KOLOM()-4;4)))

 

[AlexCEL]

Het belang van een representatief voorbeeld is weer aangetoond. De datumnotatie in je eerste en tweede voorbeeld is niet gelijk...

In bijlage heb ik de kortste en in mijn optiek minst foutgevoelige formule uit dit draadje (die van Marcel uit post #12) gezet.