[Excel] formule voor voortschrijdend gemiddelde *

[Zip]

Ik wilde graag wat hulp inroepen bij een probleempje. Ik ben bezig om iets automatisch op te zetten maar blijf met een kleine uitdaging zitten.

VB
-----

(KPI X) Jan Feb Mrt April enz.
Realisatie
Target 100 110 130 130
Bereik


(KPI Y)
Realisatie
Target 50 65 40 60
Bereik


In een Dashboard rapporteer ik een Target tm maand(voortschrijdend gemiddelde). Als de rapportageperiode bv. Februari is dient hij de targets jan + febr bij elkaar op te tellen en door 2 te delen .

In februari wordt dit dus voor KPI X (100+110) / 2 = 105
KPI Y (50+65) /2 = 57,5

In maart wordt dit dus KPI X (100+110+130) / 3 = 113,3
KPI Y (50+65+40) = 51,7


Bovenin het dashboard wordt de maand (feb) en de periode vermeld (2)

Als ik de maand en periode verander in de actuele maand; met welke formule kan ik dit automatisch oplossen.
(Dus zoeken naar de maand (bv. febr), optellen van de getallen, en delen door de periode)


Bovenstaande gaat gelden voor 50 vestigingen dus het is belangrijk dat ik dit kan automatiseren.

Alvast bedankt voor de reacties.

[Zip]

Niemand die hier wat moois van kan maken?

[Maasluip]

Die berekening is toch gewoon het gemiddelde? De functie AVERAGE berekent alleen het gemiddelde over ingevulde waardes, dus als de functie over 10 velden gaat en je eerst 2 waardes invult, wordt voor de overige 8 niet 0 ingevuld.

[F_J_K]

Zip schreef op dinsdag 05 september 2006 @ 09:10:
Niemand die hier wat moois van kan maken?

Oh ik weet zeker dat er hier mensen rondlopen die er iets heel moois van kunnen maken. Echter kan ik me voorstellen dat de meesten geen zin hebben om er een kwartier over na te denken als je alleen maar een probleemstelling neerzet zonder een eerste opzet doet van de oplossingsrichting die je zelf in gedachten had

Ik denk dat je met de functies HORIZ.ZOEKEN() (resp. verticaal) en de functie INDEX() of VERSCHUIVING() al een heel eind komt. Kijk daar dus even naar

Natuurlijk kan met VBA "alles", maar dat zou ik zo veel mogleijk vermijden voor zoiets.

edit:
Ik heb trouwens de topictitel wat aangepast, "Excel formule" zegt zo weinig anders dan het feit dat het niet over Outlook of OOo gaat

[ Voor 9% gewijzigd door F_J_K op 05-09-2006 09:25 ]

[Zip]

De berekening dient NIET het gemiddelde te zijn van het hele jaar (de maanden zijn jan tm dec) maar het gemiddelde TOT de betreffende maand. Bij Verticaal en Horizontaal zoeken zou ik alleen de waarde van de betreffende maand krijgen. (Samengestelde formules zouden misschien wel kunnen voldoen maar dat is een beetje moeilijk voor mij. Ik ben een financieel iemand geen ICT.)

Ik ga nu de functies index en verschuiving even bekijken c.q. uitproberen.

[Maasluip]

Dan zou ik eens beginnen met de data hier neerzetten zoals je hem ook in Excel hebt, anders wordt het nogal koffiedik kijken hoe de formule er precies uit moet zien.
Zoals je het nu beschrijft zou ik denken aan een AVERAGE($A$1:A1) en die dan verder slepen zodat in de volgende kolomen ($A$1:B1), ($A$1:C1) etc. komt te staan. Eventueel aangevuld met een vermenigvuldiging per maand.

[Zip]

Details:


Dashboard


De formule dient vanuit Target tm maand uit het dashboard met behulp van periode 7 en/ of juli ( of 2 en febr) het gemiddelde tm de betreffende maand te vermelden.

Eigenlijk is de som van de targets in de detaillijst al voldoende. Dus als het juli is dat hij alle targets tm juli bij elkaar optelt. Dan kan ik hem hem makkelijk door het periodenumer delen. (Elke maand veranderen we het periode nummer en maand zodat de formule automatisch geupdate wordt ).

Excuses voor de slechte kwaliteit plaatjes. Als je hem even opslaat kan je het wat beter zien.

Ps. De som van de targets dient dus (ipv 48) in juli 267 te zijn (gedeeld door 7 is 38) en in februari 67 (gedeeld door 2 is dan 33

[ Voor 8% gewijzigd door Zip op 05-09-2006 14:07 ]

[Maasluip]

Zip schreef op dinsdag 05 september 2006 @ 13:46:

Eigenlijk is de som van de targets in de detaillijst al voldoende. Dus als het juli is dat hij alle targets tm juli bij elkaar optelt.

Maar dat is toch makkelijk? In cel I7 zet je '=SUM($C$7:H7)' en die formule sleep je verder tot onder de andere maanden (slepen dus door het kleine vierkantje rechtsonder de aktieve cel te klikken en te slepen)

Dan kan ik hem hem makkelijk door het periodenumer delen. (Elke maand veranderen we het periode nummer en maand zodat de formule automatisch geupdate wordt ).

En dat kan automatisch door i.p.v. SUM de formule AVERAGE te gebruiken.

Ik zie dat je een Nederlandse versie van Excel hebt, je moet dan waarschijnlijk SOM en GEMIDDELDE gebruiken.

[Zip]

De formule dient in het dashboard cel E10 te worden gezet. In de detaillijst (I7 zitten al formules om de realisatie bij te werken). De detaillijst gaat ook naar vestigingen. Die moeten niets anders zien als realisatie, target (budget) en bereik.

Ik hoop dat het een beetje helder is.

[Maasluip]

En die in het dashboard moet dan het gemiddelde tot die maand laten zien? Dan maak je een lijstje met voortschrijdende gemiddelden per maand (zoals ik hierboven postte) en zet je in je dashboard een VLOOKUP op de juiste maand (want ik zie dat die in je dashboard ook in F4 staat).

[onkl]

F_J_K gaf je het antwoord al.
Probeer eens iets als
=SOM(VERSCHUIVING(1!C1,0,0,1,dashboards!F3))/SOM(VERSCHUIVING(1!D1,0,0,1,dashboards!F3))
in je dashboard te zetten (en daar een iets minder vertederend hippe naam voor te verzinnen ).
het stukje tussen de haakjes bij verschuiving zegt "Begin bij cel C1 op blad 1. Ga 0 cellen naar beneden en 0 naar rechts (nog steeds C1) en laat daar een blok beginnen van 1 hoog en F3 breed." Vervolgens sommeer je dat en deel je het door je cummulatieve target (zelfde methode) om je realisatiefractie (wat jij bereik noemt) te becijferen.

Maassluips methode werkt overigens ook uitstekend, behalve dat je beter HLOOKUP, ik denk HORIZ.ZOEKEN in NL kan gebruiken

[ Voor 9% gewijzigd door onkl op 05-09-2006 14:31 ]

[Zip]

Dat zou kunnen maar het beste is om dit in 1 formule te vangen. Alternatief is dat ik gewoon een som functie tm de maand erin zet en de daaropvolgende maand alleen de formules opdate met zoeken en vervangen.

Dus bv. SOM A2:I2 en de volgende maand de alle I's vervangen door J. Dan delen door het periode nummer.

Het liefst zou ik dit echter in een formule vangen die het zonder omwegen kan. Anders doe ik de bovenstaande oplossing.

[Maasluip]

Zip schreef op dinsdag 05 september 2006 @ 14:39:
Dat zou kunnen maar het beste is om dit in 1 formule te vangen. Alternatief is dat ik gewoon een som functie tm de maand erin zet en de daaropvolgende maand alleen de formules opdate met zoeken en vervangen.

Dus bv. SOM A2:I2 en de volgende maand de alle I's vervangen door J. Dan delen door het periode nummer.

Het liefst zou ik dit echter in een formule vangen die het zonder omwegen kan. Anders doe ik de bovenstaande oplossing.

Aaargh! Je leest mijn posts niet eens? Als je $A$2 neerzet i.p.v. A2 dan kun je de formule gewoon slepen en AVERAGE is hetzelfde als SOM en dan delen door het aantal meetpunten.

[Zip]

Maasluip bedankt maar dit was niet de oplossing zoals ik zocht omdat je dan weer extra lijstjes moet maken die ik niet in de rapportage wil terugzien (andere mensen raken hierdoor in verwarring).

Het is uiteindelijk gelukt via de som verschuiving functie zoals onkl aangaf. Ik heb even lopen kl.....ten. Duurt even voordat je de functie goed begrijpt.

=SOM(VERSCHUIVING('1'!C12;0;0;1;$U$1))/$U$1 (waarbij U1 de periode nummer is). Indien het periodenummer verandert naar aug doet hij het auto goed.


In ieder geval bedankt voor alle reacties