Avatar billede M_M_Hansen Nybegynder
22. oktober 2014 - 21:06 Der er 7 kommentarer og
1 løsning

Dynamisk Vægtet gennemsnit

Hej Eksperter!

Jeg sidder og "hygger" med et excel-projekt hvor jeg har en udfordring.

Jeg har en udfordring med at lave en formel som er dynamisk i forhold til udvidelse af data, da der kommer nyt hvert år.

Der skal udregnes et gennemsnit for hver formel som beskriver forholdet mellem en omkostning og antal bruger af denne. Eksempel nedenfor.

                          A          B          C          D          E          F          G          H          I          J
År                  : 2010  2010  2010  2010  2010  2011  2011  2011  2011  2011
Omkostning:  100      230    150    175    125    105    225    155    160    100
Brugere        :    5          8          7        3        2          4        7        6          3        2

Det jeg pt. gør er følgende:

                          A          B          C        D        E        F        G        H          I          J
År                  : 2010  2010  2010  2010  2010  2011  2011  2011  2011  2011
Omk./Brug :    20    28,75  21,42  58,33  62,5  26,25  32,14  25,83  53,3    50           
2010  =MIDDEL(A2:E2)=38,20
2011  =MIDDEL(F2:J2)=37,51


Det understreget er manuelt arbejde hver gang, jeg håbede på kunne gøres smartere, således at jeg i én formel kunne finde gennemsnittet af et pågældende år, også selvom jeg tilføjede nye år fremadrettet?

Som en ekstra krølle på halen vil det optimale være at Excel selv søgte efter "Omkostning" og "Brugere" f.eks. via "Match/SAMMENLIGN" eller lign. for at sikre dynamikken.

Håber nogle har ideer til løsning, da jeg efterhånden er ret udtømt!

Mvh
MMHansen
Avatar billede sdh Mester
22. oktober 2014 - 22:57 #1
Har lavet følgende løsning:

=middel.hvis($C$3:$X$3;B7;$C$4:$X$4)/middel.hvis($C$3:$X$3;B7;$C$5:$X$5)

hvor årstal er angivet i celle b7.

Formlen er lavet som et vægtet gennemsnit - dvs at resultatet for 2010 giver 31,2 og 33,86 for 2011.

Det giver ingen mening at beregne gns som du har vist:

eksempelvis 1 bruger a 100 = 100
          10 bruger a  10 = 100
= gennemsnit (100+10)/2 = 55 (din beregning)

hvor vægtet gennemsnit giver (200/11) = 18,18.
Avatar billede xl-Enthusiast Ekspert
23. oktober 2014 - 06:49 #2
Jeg ville beregne vægtet gennemsnit som følger:

=SUMPRODUKT(Område med omkostninger; Område med brugere)/SUM(Område med brugere).

For de to år får jeg:

2010: 166,60
2011: 163.86

http://gupl.dk/715363/
Avatar billede xl-Enthusiast Ekspert
23. oktober 2014 - 09:07 #3
Jeg havde ikke læst spørgsmålet ordentligt, så her er lige en rettet fil:

http://gupl.dk/715364/
Avatar billede M_M_Hansen Nybegynder
23. oktober 2014 - 20:05 #4
Tak for jeres svar! Fandt dog en anden måde at gøre det på hvor den var mere dynamisk. Lavede en macro hvor vi kombinerede match med sumifs som så gjorde værket :)


Men synes i skal have point for forsøg så smid nogle svar! :)
Avatar billede sdh Mester
23. oktober 2014 - 20:26 #5
Her et svar:-)
Avatar billede xl-Enthusiast Ekspert
23. oktober 2014 - 20:32 #6
Jeg samler ikke på point, men giv dem til sdh, hvis han er interesseret.

For min part ville jeg meget hellere have haft at vide på hvilken måde du gjorde det mere dynamisk end ved at klikke på pilen og så vælge et nyt årstal i A6 (og i øvrigt også hvad du forstår ved "vægtet gennemsnit", jf. din overskrift og sdhs kommentar). Derved havde jeg (og andre) måske haft chancen for at lære noget nyt i stedet for bare at få at vide, at du fandt en anden måde.

Én ting er at du ikke kan/vil bruge fremkomne løsningsforslag, fair nok, men det ville også være fair nok, hvis du så ville give en rimelig detaljeret forklaring så dem der har forsøgt at hjælpe dig kunne begribe hvorfor deres løsningsforslag ikke duede.
Avatar billede M_M_Hansen Nybegynder
26. oktober 2014 - 13:14 #7
xl-Enthusiast:
Jeg synes du fortjener lidt respons så! :)

Det er til en benchmark jeg lavede i forbindelse med arbejdet, så jeg forsøgte at forsimple det en smule, men konceptet var det samme!

Jeg tror måske jeg havde misforstået hvad vejet gennemsnit var, så det var nok svært at give et svar :p Derfor jeg forsøgte at vise hvilket resultat jeg vil frem til.

Vi lavede en macro funktion som gjorde at når jeg skrev f.eks. 15, så valgte den hele rækken istedet for kun 1 felt, som så sådan ud ->
'Function Rækkefunktion(Række As String) As Range
'Set Rækkefunktion = Datasheet.Range(Række & ":" & Række) 
'End Function

Derefter var det at indarbejde den i sdhs formel så den endte med at hedde sådan:
=SUMIFS(Rækkefunktion(MATCH(AB99;data!$A:$A;0));data!$4:$4;Årstal)/
SUMIFS(Rækkefunktion(MATCH($AC$53;data!$A:$A;0));data!$4:$4;Årstal)/

Matchen søger så efter en tekst i AB99=Omkostninger og AC53=Brugere så den finder den linje i dataarket den vil returnere. Årstallet repræsentere så det år den vil finde så jeg kan genbruge formlen og så videre.

Håber det giver mening og du kan bruge dette mere end point :)
Avatar billede xl-Enthusiast Ekspert
26. oktober 2014 - 16:18 #8
Tak.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Vi har et stort udvalg af Excel kurser. Find lige det kursus der passer dig lige her.

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester