Sum og gennemsnit af tal i intervaller
Lad mig starte med at sige tak til Lykke i Aarhus for inspirationen til denne løsning - og dermed til artiklen.
Udfordringen var at lave en formel, der kunne beregne gennemsnittet af nogle tal, opdelt i nogle intervaller. Udgangspunktet var FREKVENS-funktionen, som kan tælle, hvor mange tal, der er i et interval, se artiklen Tal i Interval.
I den konkrete situation skal denne funktion også bruges i forbindelse med beregning af gennemsnittet, men i første omgang gælder det om at få beregnet summen indenfor hvert interval. Dette kan FREKVENS ikke løse. SUM, SUM:HVIS osv. kan heller ikke umiddelbart, da de kun kan tage en betingelse og intervalberegning kræver to). Opgaven kan løses på to måder, enten ved hjælp af to SUM.HVIS, der trækkes fra hinanden, eller ved hjælp af SUMPRODUKT().
Beregn Summen
Antag at observationerne (tal mellem 1 og 100) står i A1:A4000 og jeg vil gerne have beregnet gennemsnittet for intervallerne 1-10, 11-20, 21-30... til ...91-100.
I fx kolonne E skriver jeg mine intervalgrænser 0, 10, 20, 30... til ...100). I kolonne G vil jeg nu have beregnet summen. Det kan fx gøres med
=(SUM.HVIS($A$1:$A$4000;"<="&E2)-SUM.HVIS($A$1:$A$4000;"<="&E1))
Observer at jeg har 0 med i E1 selv om det er undergrænsen for det laveste interval, som man ellers ikke normalt skriver. Det er heller ikke nødvendigt her, men jeg taget det med for overskuelighedens skyld.
Det andet, der er vigtigt er brugen af '&' i betingelserne. Da logiske operatorer som >, < osv., kræver anførselstegn omkring, vil det blive opfattet som en tekst. Fx vil man kunne skrive =(SUM.HVIS($A$1:$A$4000;"<=10") og formlen vil så finde summen af alle de tal, der er mindre end 10. Denne metode virker dog ikke her, da =(SUM.HVIS($A$1:$A$4000;"<=E2") ikke vil fungere fordi Excel opfatter E2 som en tekst, og ikke som en cellereference. Ved at afslutte teksten lige efter opreratoren, og så sammensætte denne med cellereferencen ved hjælp af '&', kommer det til at fungere.
Når ovenstående formel kopieres nedad, beregnes summen inden for hvert interval.
Alternativt kunne man have brugt SUMPRODUKT, så havde man sparet anførselstegn og '&'. SUMPRODUKT-formlen kan se ud som her:
=SUMPRODUKT(($A$1:$A$4000<=E2)*($A$1:$A$4000>E1)*($A$1:$A$4000))
Beregn gennemsnittet
Kolonne F indeholder fra F2 til F11 en helt almindelig FREKVENS-funktion oprettet som matrixformel, som tæller antallet af tal i hvert interval.
Nu kan intervalgennemsnittene beregnes som =G2/F2 og kopieres nedad. Vil man spare G-kolonnen, kunne formlen se således ud
=(SUMPRODUKT(($A$1:$A$4000<=E2)*($A$1:$A$4000>E1)*($A$1:$A$4000)))/F2
eller selvfølgelig tilsvarende med SUM.HVIS funktionen.
Det færdige resultat kunne se ud som følger:
PS! Teksterne er lavet med denne formel:
="Gennemsnit i Intervallet mellem "& E1+1 & " og "& E2