vrijdag 17 juni 2016

Excel: lastige berekening gewogen percentage opgelost met matrix functies

Gewogen percentage

Een ogenschijnlijk simpele vraag leidt tot het nodige denkwerk.


Een collega kwam met het volgende simpele tabelletje:

Categorie # Items # Incidenten
a 1 1
b 1 1
c 5 1
d 1 1
e 1 1
f 1 3
g 1 1
h 1 1
i 3 1
j 1 1

De vraag was hoe het gewogen percentage te berekenen per categorie. Het heeft enige hoofdbrekens gekost maar uiteindelijk hebben we de oplossing.

In dit geval weegt het aantal incidenten zwaarder naarmate er minder items zijn. Zijn er minder items zijn, dan geldt het omgekeerde. Dit betekent dat we het aantal incidenten moeten delen op het aantal items.

Dit leidt in kolom D tot een simpele formule:

=C2/B2

Deze formule kunnen we dan doortrekken naar beneden. Om nu tot een gewogen percentage te komen moeten we de uitkomst delen door de som van alle delingen:

=SOM($C$2:$C$11/$B$2:$B$11)

Voor de juiste berekening moeten we hier een matrix formule van maken. Dat wil zeggen dat we de functie moeten bevestigen door op CTRL SHIFT + ENTER te drukken (in plaats van gewoon op ENTER)

De uiteindelijke formule wordt dan:

{=C2/B2/(SOM($C$2:$C$11/$B$2:$B$11))}

Deze kunnen we weer doortrekken naar beneden. Het uiteindelijk resultaat wordt dan:

Categorie # Items # Incidenten gewogen per categorie in %
a 1 1 9,49%
b 1 1 9,49%
c 5 1 1,90%
d 1 1 9,49%
e 1 1 9,49%
f 1 3 28,48%
g 1 1 9,49%
h 1 1 9,49%
i 3 1 3,16%
j 1 1 9,49%

Matrix berekening

Het verschil tussen een matrix berekening en een gewone berekening is duidelijk te zien in het volgende voorbeeld:

Categorie # Items # Incidenten gewogen per categorie in % Matrix Normaal
a 1 1 9,49% 10,53333 1
b 1 1 9,49% 10,53333 1
c 5 1 1,90% 10,53333 0,2
d 1 1 9,49% 10,53333 1
e 1 1 9,49% 10,53333 1
f 1 3 28,48% 10,53333 3
g 1 1 9,49% 10,53333 1
h 1 1 9,49% 10,53333 1
i 3 1 3,16% 10,53333 0,333333
j 1 1 9,49% 10,53333 1

In de voorlaatste kolom staat de formule:

{=(SOM($C$2:$C$11/$B$2:$B$11))}

Daar wordt het totaal berekend van alle delingen per rij.

In de laatste kolom staat de formule:

=(SOM($C$2:$C$11/$B$2:$B$11))

Daar wordt het totaal van de deling per rij. De functie SOM is daar in feite overbodig. We hadden er ook kunnen neerzetten:

=($C$2:$C$11/$B$2:$B$11)

of gewoon:

=C2/B2

en dan die formule doortrekken.

Bijbehorende bestand ExcelGewogenPercentage.xlsx is te downloaden via:
Een reactie plaatsen