Voor de sterrenrating kopieerde ik een formule uit een ander blog. Beter goed gejat dan slecht bedacht, right? De formule werkte, maar werd niet uitgelegd. Ik wilde ‘m begrijpen, zeker omdat ik de formule REST (in het Engels MOD) en AFRONDEN.NAAR.BOVEN (ROUNDUP) nog niet eerder heb gebruikt. Ik zocht het uit en in dit blog wordt de sterrenrating formule uitgelegd.
=IF(C$1<=$B3;1;IF(ROUNDUP($B3;0)=C$1;MOD($B3;1);0))
Uitgangspunten voor de sterrenrating formule
De formule zet de score van de film om in 10 getallen. Op basis van die getallen, maakt conditionele opmaak er hele, halve of lege sterren van. Verder is er voor elke ster een referentiecijfer nodig, die nodig is voor de formule. Die staat in de eerste rij.
Conditionele opmaak
Bij deze formule zijn 3 soorten sterren mogelijk door het gebruik van conditionele opmaak:
- Er is een hele ster (bij uitkomst 1 bij de formule),
- een halve ster (uitkomst tussen 0,5 en 1) en
- een lege ster (uitkomst van de formule is kleiner dan 0,5).
Het mooie van deze formule is dat die werkt voor elk van de sterren, je hoeft dus niet per rij of per kolom de formule aan te passen.
Een referentiecijfer
Elk van de 10 sterren heeft een referentiecijfer (RC) in rij 1: ster 1 krijgt nummer 1, ster 2 nummer 2 enz. De formule verandert steeds mee, omdat die aan de ene kant steeds verwijst naar het betreffende referentiecijfer en aan de andere kant gebruik maakt van de vaststaande score. Delen van de formule vastzetten doen je door het gebruik van het dollarteken. Deze dollartekens kun je voor de uitleg in dit blog vergeten.
Sterrenrating formule in het kort uitgelegd
- Een gele, gevulde ster krijg je bij uitkomst 1 van de formule, omdat de score van de film groter of gelijk is aan het referentiecijfer van de ster.
- De formule geeft als uitkomst het cijfer achter de komma. Deze is groter dan 0 en kleiner dan 1. Met de conditionele opmaak wordt dit lege ster als de uitkomst tussen 0 en 0,5 ligt of een halve ster als de uitkomst tussen 0,5 en 1 ligt.
- Is de uitkomst groter dan 0? Dan is de uitkomst 0, dat wordt een lege ster.
Sterrenrating formule uitgelegd in chronologische volgorde
Neem bijvoorbeeld de score 8,7 van Ford v Ferrari. Hoe werkt de sterrenrating formule dan?
Eerste deel van de formule
Bij een 8,7 wil ik dat de 1e tot de 8e ster geheel gevuld zijn. Als het referentiecijfer van de ster kleiner of gelijk is aan 8,7 dan wordt die gevuld. Voor de 8e ster geldt J1<=B3; het referentiecijfer (J1) is kleiner of gelijk aan de score (B3). Deze cel krijgt daarom uitkomst 1.
Tweede deel formule
De formule geeft bij de 9e ster geen hele ster, want de score 8,7 is niet groter of gelijk aan referentiegetal 9. Dan gaat het tweede deel van de formule in werking.
De als formule gaat hier als volgt: Als we de score van de film naar boven afronden zonder cijfers achter de komma, is deze score dan gelijk aan het referentie getal? Het antwoord is hier altijd ja. Dan wordt de MOD (de REST) formule gebruikt om het cijfer achter de komma te bereken als uitkomst.
De MOD formule neemt steeds stapjes van 1 weg bij het oorspronkelijke basis getal en houd als uitkomst 0,7 over. Met de conditionele opmaak wordt hier een halve ster van gemaakt. Was de uitkomst bijvoorbeeld 0,2 (bij een score van 8,2) dan werd hier door de conditionele opmaak een lege ster van gemaakt.
Derde deel formule
De score is dus lager dan het referentiegetal (8,7 is lager dan 10) EN de score is afgerond naar boven niet gelijk aan het referentiegetal (8,7 is afgerond 9 en daarmee niet gelijk aan 10). Dan is de uitkomst 0 en met conditionele opmaak wordt dat een lege ster.
Sterrenrating formule uitgelegd in 2 onderdelen
REST formule
Wanneer je een restgetal wilt uitrekenen, gebruik je de REST formule. (MOD in het Engels) Je geeft een basis getal aan, je geeft aan door welke waarde je deze wilt delen en de REST formule geeft aan welke waarde er overblijft na het delen door dit gehele getal.
Je wilt bijvoorbeeld weten hoeveel je overhoudt als je 100 (basis getal) deelt door 34 (de deler). De formule is dan REST(100,34), je krijgt als antwoord 32. Je kunt twee keer 34 van 100 afhalen, je houdt dan 32 over. Daar kun je geen 34 meer vanaf halen.
REST formule in de sterrenrating uitgelegd
De REST formule gebruik je voor de halve of lege ster. Je deelt 8,7 (in cel B3) door 1. De uitkomst van de formule is wat overblijft, de rest. In dit geval is dat het cijfer achter de komma. Op basis van conditionele opmaak wordt daar een halve of een lege ster van gemaakt.
Andere voorbeelden REST formule
Je kunt de REST formule gebruiken als je een groepsindeling wilt maken en je weet niet of het handiger is om 54 mensen in groepen van 4, 5 of 6 op te splitsen. (In 6, dan hou je niemand over)
REST = MOD in het Engels
MOD komt van modulair rekenen; geheeltallig rekenen met een getal als bovengrens. De modulus. De analoge klok is een voorbeeld, waarbij met modulus 12 wordt gerekend. Als het 9 uur is, dan staat de klok 8 uur later niet op 17 uur, maar op 17-12 = 5 uur.
AFONDEN.NAAR.BOVEN formule
De formule AFRONDEN.NAAR.BOVEN spreekt voor zichzelf en hoeft eigenlijk niet te worden uitgelegd. Wel is belangrijk om te weten dat de afronding ALTIJD naar boven gaat. Waar je in het dagelijks leven 5,2 afrondt naar een 5, wordt bij de formule AFRONDEN.NAAR.BOVEN een 5,2 afgerond naar een 6.
AFONDEN.NAAR.BOVEN formule binnen de sterrenrating uitgelegd
In deze formule worden scores die niet groter of gelijk zijn aan het referentiegetal naar boven afgerond. Zijn ze dan exact het referentiegetal (wat het geval zou moeten zijn), dan wordt het rest getal berekend. Dit onderdeel in de formule is daarmee een dubbelcheck.
Andere voorbeelden AFONDEN.NAAR.BOVEN formule
Afronden naar boven kwam ik in deze formule voor het eerst tegen en ik kan eerlijk gezegd geen voorbeelden bedenken waarin je deze formule nodig zou hebben.
Sterrenrating formule uitgelegd
De sterrenrating formule is op 3 manieren uitgelegd. Ik hoop dat je er iets aan had. Wellicht ga je de REST of AFRONDEN.NAAR.BOVEN formules nog eens gebruiken.
Ben of ken je een beginner die meer wil leren over spreadsheets, formules en dashboards maken? Schrijf je in voor de nieuwsbrief; je weet dan als eerste welke dashboards we gaan bouwen, krijgt oefenmogelijkheden, downloads en je kunt zelf input geven over welke dashboards je graag zou willen maken.