Gewogen percentage
Een ogenschijnlijk simpele vraag leidt tot het nodige denkwerk.
Een collega kwam met het volgende simpele tabelletje:
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:
Matrix berekening
Het verschil tussen een matrix berekening en een gewone berekening is duidelijk te zien in het volgende voorbeeld:
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.
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:
Voor verder Excel tips klik hier.
Andere blogs over Excel
Voor het beste overzicht verwijs ik naar een pagina van mijn website: http://www.walmar.nl/spreadsheets.asp
Reacties