Tagarchief: Afronden

Reken-nauwkeurigheid van Excel



Het symbool hiernaast betekent ongeveer gelijk aan.

Als we met Excel aan het rekenen zijn, nemen we daar natuurlijk geen genoegen mee! Toch moeten we ons altijd bewust zijn van de beperkingen van dit hulpmiddel wat rekenen betreft.

In dit artikel zullen we aan de hand van enkele voorbeelden laten zien dat Excel zeker ‘rekenfouten’ maakt. Soms worden dit bugs in Excel genoemd, maar dat is niet juist: het is onder andere de consequentie van het binaire karakter van onze computers.
Uiteraard kijken we ook hoe we in ons dagelijks werk rekening kunnen houden met dit ‘vreemde’ gedrag van Excel (en ieder ander rekenprogramma).

‘Rekenfouten’

Nog steeds als ik een (simpel) rekenapparaat in handen krijg moet ik gauw even testen hoe nauwkeurig die werkt: 1 delen door 3, het resultaat vermenigvuldigen met 3 en van dat resultaat 1 aftrekken. Het resultaat zou dan 0 moeten zijn.

In het tabblad Simpel van het Voorbeeldbestand staat een dergelijke berekening. In cel B6 is te zien, dat Excel in ieder geval vindt dat het eindresultaat exact gelijk is aan nul.

NB1 in kolom C is de functie Formuletekst gebruikt; deze zet een eventuele formule in de cel, die je tussen de haakjes meegeeft (in het voorbeeld cel B3), om in een tekst. Deze functie bestaat vanaf Excel 2013.

NB2 als de betreffende cel geen formule bevat dan is het resultaat van deze functie #N/B (Niet Beschikbaar).
Dit kunt u op de volgende manier ondervangen: =ALS.FOUT(FORMULETEKST(B3);”Geen formule”)

Maar eigenlijk is het niet zo logisch, dat het resultaat nul is! We weten allemaal dat, als we de breuk 1/3 omzetten naar een decimaal getal, we nooit klaar zijn; het aantal drietjes achter de komma is oneindig.

In het tweede voorbeeld op het tabblad Simpel staat dezelfde berekening, maar nu zijn alle resultaten weergegeven met 20 decimalen.

Duidelijk is te zien, dat Excel bij het berekenen van 1/3 er na 15 decimalen mee ophoudt. In de dagelijkse praktijk kunnen we daar meestal ook wel mee leven.
Maar: als we dat resultaat weer met 3 vermenigvuldigen komt Excel toch op 1 uit en niet op 0,99999… Wie houdt nu wie voor de gek? Blijkbaar legt Excel intern het getal 1/3 anders vast dan wij zien. Dat is natuurlijk ook wel logisch: wij zijn gewend om te rekenen in het decimale stelsel, terwijl computers in het binaire stelsel maar tot 2 kunnen tellen, nog erger eigenlijk maar tot 1. Zij kunnen alleen maar werken met nullen en enen; voor gehele getallen kom je dan een heel eind (als je maar genoeg bits op een rijtje zet), maar bij decimale getallen komen computers al snel ‘in de problemen’.

Met dit in het achterhoofd is het eigenlijk verwonderlijk dat we niet iedere dag Excel op rekenfouten betrappen.

NB na wat googlen: “Excel werkt met een aangepaste 1985-versie van de IEEE 754 specificatie. De implementatie van Excel omvat conversies tussen binaire en decimale weergaven, wat leidt tot een nauwkeurigheid die gemiddeld beter is dan men zou verwachten van een eenvoudige 15-cijferige nauwkeurigheid.

Nog een voorbeeld: we laten Excel 1/9 uitrekenen. Dit zou een getal met oneindig veel decimalen gelijk aan 1 moeten opleveren; ook hier laat Excel maar 15 eentjes zien.
Daar tellen we 1 bij op en van dat resultaat trekken we weer 1 af. Als we die uitkomst aftrekken van de eerste berekening komt er precies nul uit.

Maar doen we hetzelfde met 1/9000 dan komt er geen nul meer uit! Het resultaat is ‘veel’ groter dan waar we mee begonnen.

NB1 6,17E-17 is de zogenaamde wetenschappelijke notatie van een getal; dit betekent dat we 6,17 moeten vermenigvuldigen met 10 tot de macht -17 (10^-17) ofwel delen door 10^17 ofwel de 6 komt op de plaats van de 17e decimaal.

NB2 de notatie E-17 moet niet verward worden met de in de wiskunde gebruikte exponentiële functie e:

Nog meer voorbeelden

Helaas is de reeks voorbeelden van ‘onverklaarbare rekenfouten’ in Excel schier onuitputtelijk. In het tabblad Vb van het Voorbeeldbestand vindt u er nog een paar:

In Vb3 is het getal 7745,11319561 ingevoerd; Excel verandert dat op eigen houtje.

En wat te denken van het verschil tussen 7,11 en 7,1 of 7,11 en 7,01?

Hiernaast staat een simpele berekening: het tweede en derde getal worden afgetrokken van het eerste. Iedereen ziet direct dat daar 0 uit moet komen. Gelukkig: Excel is het daarmee eens.

Maar wat nu? In kolom Q doen we dezelfde berekening, maar tellen daar dan nog 0 (nul, zero, niente, nada) bij op. Het resultaat is schrikken.

Het mag duidelijk zijn, dat we altijd alert moeten blijven bij resultaten van berekeningen in Excel. Ik kan me niet voorstellen, dat een bedrijf failliet gaat wanneer het laatste van 15 significante cijfers niet helemaal klopt. Maar er kunnen wel problemen ontstaan bij geautomatiseerde spreadsheets: verticaal zoeken levert verkeerde resultaten, controletellingen komen niet op nul uit, formules met Als leveren een foutieve uitkomst.

Afronden

Veel van de hierboven geschilderde problemen kunnen we oplossen door tussen- en/of eindresultaten af te ronden.

In het tabblad Afronden van het Voorbeeldbestand ronden we in kolom C iedere tussenstap van het vierde voorbeeld af op 5 decimalen.

NB 5 decimalen is ruim voldoende als het bijvoorbeeld een financieel overzicht betreft. De diverse eindresultaten dienen dan op 2 decimalen aan te sluiten.

Sneller, handiger en efficiënter is het om alleen de einduitkomst af te ronden:

In het tabblad Afronden van het Voorbeeldbestand vindt u diverse overzichten waarmee u kunt zien wat de effecten van de verschillende afrondingsmethoden zijn. Ook ziet u daar de consequenties van negatieve waarden voor het aantal decimalen.

Wat als?

De aanleiding voor dit artikel was een vraag van iemand, die zich afvroeg waarom zijn metingen-systeem in Excel zich ‘ineens zo raar gedroeg’.
Per monster worden 5 metingen gedaan; om te bepalen hoe groot de spreiding van de metingen is wordt een standaarddeviatie berekend.

Omdat het regelmatig voorkomt dat de metingen allemaal dezelfde waarde opleveren moet dit zichtbaar gemaakt worden door weergave van de tekst SD=0. Blijkbaar is dit jaren goed gegaan maar ineens kwamen er vreemde resultaten.
In het tabblad SD van het Voorbeeldbestand zijn in kolom B vijf meetresultaten opgevoerd; allemaal de waarde -1,6108. In cel C9 is met de functie STDEV de standaarddeviatie hiervan bepaald.
Waar we nul zouden verwachten, blijkt Excel een kleine waarde te genereren, waardoor in cel G9 niet de verwachte tekst (SD=0) tevoorschijn komt.

NB1 de functie STDEV is een ‘oude’ functie, die uit comptabiliteitsoverwegingen nog in Excel gebruikt kan worden.

NB2 de functie STDEV berekent de deviatie voor een steekproef. De werking is dus gelijk aan de ‘nieuwe’ functie STDEV.S.
Voor het bepalen van de deviatie voor de hele populatie moet u de functie STDEV.P (nieuw) of STDEVP (oud) gebruiken.

Op het tabblad SD van het Voorbeeldbestand hebben de 4 deviatie-berekeningen een voorwaardelijke opmaak gekregen: als het resultaat nul is, dan wordt het vak groen gekleurd, anders rood.

LET OP in kolom D staan dezelfde deviatiewaarden als in kolom C. De celeigenschappen hiervan zijn zodanig gewijzigd, dat er geen decimalen zichtbaar zijn. Nu lijkt het resultaat wel nul te zijn, maar de voorwaardelijke opmaak laat zien, dat dit niet zo is.
We hebben alleen de opmaak gewijzigd, niet de inhoud van de cel.

in cel G11: =ALS(AFRONDEN(C9;10)=0;”SD=0″;C9)

Wat te doen, als we toch de Als-functie willen gebruiken of voorwaardelijke opmaak aan cellen willen toevoegen?
Eén oplossing hebben we hiervoor al gezien: gebruik de functie Afronden.

Een andere methode is om binnen de Als-functie de mogelijke onnauwkeurigheid in te bouwen. In cel G10 staat de formule
=ALS(C9<0,00000000000001;”SD=0″;C9)

NB1 omdat een standaarddeviatie altijd positief is werkt bovenstaande formule prima. Als je niet zeker weet of de afwijking positief of negatief zal zijn dan is het beter om de volgende formule te gebruiken:
=ALS(ABS(C9)<0,00000000000001;”SD=0″;C9)

NB2 uiteraard hangt het van de situatie af, welke grens er in de vergelijking moet worden ingevuld. In dit geval zou de volgende formule ook voldoen:
=ALS(ABS(C9)<1e-10;”SD=0″;C9)

Allemaal gelijk of niet?

In het voorbeeld op het tabblad SD van het Voorbeeldbestand wordt de functie STDEV ook gebruikt om te beoordelen of alle metingen hetzelfde zijn.

Dat kan natuurlijk ook op een andere manier.
Hiernaast is in cel C18 de minimum-waarde van de metingen bepaald en in cel C19 het maximum.
In cel C20 wordt dan gekeken of deze twee gelijk zijn; zo ja dan zijn alle metingen gelijk.

Uiteraard is de tussenstap niet per se nodig. In cel D20 staat de formule:
=MIN(B3:B7)=MAX(B3:B7)

NB voor het bepalen van het minimum en maximum hoeft Excel geen berekening uit te voeren. We hoeven dus niet ‘bang te zijn’ dat Excel een extra onnauwkeurigheid toevoegt.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?


Gegevenstabel

wa1Ik had al lang op mijn lijstje staan om iets te schrijven over Gegevens-tabellen in Excel. Een handig hulpmiddel bij scenario-analyses.
Een artikel op de site van Chandoo was aanleiding om daar daadwerkelijk invulling aan te geven!

Even verwarring wegnemen.
In Excel kennen we twee soorten tabellen: Tabellen en Gegevenstabellen. Helder? Vóór versie 2010 werden deze tabellen Lijsten, respectievelijk Tabellen genoemd.
Laten we de nieuwere terminologie maar hanteren: in Excel is een Tabel een verzameling bij elkaar horende cellen, die door de gebruiker worden gevuld (zie ook de  artikelen Kunst en Excel en Tabellen deel 2); een Gegevenstabel daarentegen wordt door Excel automatisch ingevuld.
Cryptisch? Lees hieronder verder.

Gegevenstabel met 1 variabele

Om de werking van een Gegevenstabel uit te kunnen leggen hebben we een voorbeeld nodig. In het Voorbeeldbestand heb ik een aangepaste versie van het Chandoo-voorbeeld gebruikt:

  1. Sara wil een kopieer-bedrijfje beginnen en maakt een bedrijfsanalyse in Excel
  2. wa2Ze kan copiers leasen met een capaciteit van 100.000 kopieën per jaar voor een bedrag van € 5.000 per maand.
  3. Een ruimte heeft ze ook al op het oog voor € 1.000 per maand.
  4. De kostprijs van een kopie is 2 cent.
  5. Marktonderzoek heeft uitgewezen, dat klanten 11 cent voor een kopie over hebben.
  6. Hoeveel kopietjes moeten er dagelijks gemaakt worden om break-even te spelen; uitgaande van 50 werkweken van 5 dagen?

In het Voorbeeldbestand op het tabblad WA1 zijn deze aannames in het blok Input verwerkt.

wa3In datzelfde tabblad staat ook een blok Berekeningen:

  1. eerst het aantal kopieën: in cel G4 staat de formule =C8*C9
  2. dan moeten we weten hoeveel copiers Sara daarvoor moet leasen: het aantal kopieën gedeeld door de capaciteit van een copier. Dit moet een heel getal zijn dus in cel G5 komt de formule =AFRONDEN.NAAR.BOVEN(G4/C5;0).
    De tweede parameter (0) geeft aan dat het resultaat geen decimalen mag hebben.
  3. De formules voor de kosten, opbrengsten en de winst mogen verder geen verrassingen meer met zich mee brengen.
  4. De 3 cellen in regel 16 hebben een voorwaardelijke opmaak gekregen, zodat direct duidelijk is of er sprake is van winst of verlies. 500 kopieën per dag zijn duidelijk niet voldoende om onder de gehanteerde aannames  winst te maken.

Door nu het aantal kopieën per dag in cel C9 te wijzigen kunnen we proberen het break-even-point voor dit bedrijfje te vinden.

En juist dit is het moment, dat een Gegevenstabel zijn kracht kan bewijzen:

  1. wa4plaats ergens in een lege kolom alle aantallen, die doorgerekend moeten worden (bv 100 t/m 1000)
  2. één regel hoger in de kolom rechts daarnaast plaatsen we een verwijzing naar de cel met de winst/verliescijfers (of rechtstreeks de formule =G14-G11)
  3. selecteer dan alle cellen met aantallen, inclusief de lege cel  daarboven en alle cellen daarnaast (in het voorbeeld hiernaast dus de cellen J7 tot en met K26.
    Tip selecteer cel J7 en druk op Ctrl-A
  4. wa5kies binnen de menu-tab Gegevens in het blok Hulpmiddelen voor gegevens de optie Wat-als-analyse en daarbinnen de optie Gegevenstabel
  5. we hebben een 1-dimensionale gegevenstabel: alleen in de kolom staan waardes die door Excel moeten worden doorgerekend. In het vervolgscherm wa6vullen we dan ook alleen de kolominvoer in; we verwijzen naar de cel met het aantal kopieën (cel C9).
  6. Klik OK en Excel vult alle cellen in kolom K met de winst, die bij het betreffende aantal hoort!

NB1 als je een waarde in kolom J wijzigt, zal ook het resultaat in kolom K worden aangepast

NB2 we hebben nu alleen een formule in kolom K gezet; in een Gegevenstabel met 1 variabele kunnen echter meerdere resultaten naast elkaar berekend worden.

NB3 Excel heeft een heel speciale matrix-formule in de sheet gezet (let op de {} rond de formules in kolom K). Een consequentie daarvan is, dat je niet zomaar meer cellen/regels kunt weghalen of toevoegen. Zorg dus bij het maken van een gegevenstabel dat je ruim voldoende invoercellen hebt. Moet je later iets toevoegen dan zul je de bestaande gegevenstabel moeten weghalen en een nieuwe tabel creëren.

Tot en met 1000 exemplaren per dag wordt dit bedrijf niet winstgevend. Tot en met 400 kopietjes neemt het verlies af maar daarna schiet die weer omhoog (er moet een 2e copier geleased worden). Tussen 400 en 800 loopt het verlies weer terug; na de hik bij 800 geldt hetzelfde verhaal.

wa8wa7Om het overzicht wat flexibeler te maken zijn in het Voorbeeldbestand 2 cellen opgenomen (K4 en K5) waarmee het startaantal en de stapgrootte aangepast kunnen worden.

Vullen we daar 500 resp. 100 in, dan krijgen we direct het overzicht zoals hiernaast.
Bij 1.200 kopietjes per dag speelt Sara quitte, maar daarboven gaat het weer mis (er moet nog een copier bij!). Pas rond 2.000 exemplaren begint er een stabiele winst te ontstaan.

Gegevenstabel met 2 variabelen

wa9Maar het model sluit niet goed aan bij de realiteit: wanneer het aantal kopietjes per dag ineens groter wordt kan Sara natuurlijk niet (direct) over meer of nieuwe copiers beschikken; met de leverancier moeten van tevoren goede afspraken gemaakt worden. Voor het aantal gewenste copiers moet een inschatting gemaakt worden. Die raming nemen we in de input op.

In het tabblad WA2 van het Voorbeeldbestand is die wijziging doorgevoerd.
Ook is bij de berekening daar de formule voor het aantal kopieën aangepast; hier komt het minimum van (het aantal dagen * aantal kopieën) en (aantal copiers * capaciteit): =MIN(C9*C10;C6*C5)

Ons winst-model is nu afhankelijk van 2 variabelen: het aantal copiers en het aantal kopieën per dag.
Dus willen we een scenario-analyse doen dan zal onze gegevenstabel ook 2-dimensionaal moeten zijn:

  1. plaats ergens in een lege kolom alle aantallen kopieën, die doorgerekend moeten worden (bv 100 t/m 1000)
  2. één regel hoger in de kolommen rechts daarnaast plaatsen we de aantallen copiers, die we willen doorrekenen
  3. wa10in de cel links van de aantallen copiers (en dus net boven het aantal kopieën) komt weer een verwijzing naar de cel met de winst/verliescijfers
  4. selecteer dan de cel met de formule en het hele blok cellen met links en boven de aantallen (in het voorbeeld hiernaast dus de cellen K8 tot en met R27.
  5. kies binnen de menu-tab Gegevens in het blok Hulpmiddelen voor gegevens de optie Wat-als-analyse en daarbinnen de optie Gegevenstabel
  6. we hebben nu een 2-dimensionale gegevenstabel: in de rij staan de waardes voor het aantal copiers en in de kolom het aantal kopieën, waarvoor de resultaten door Excel moeten worden doorgerekend. In het vervolgscherm wa11vullen we dan ook allebei de invoercellen in
  7. Klik OK en Excel vult alle cellen in de gegevenstabel in.

Uit de tabel volgt dat het bedrijfje pas levensvatbaar gaat worden bij 4 kopiers en dat Sara moet proberen dan 1600 kopieën per dag te produceren.

Risicoanalyse mbv Gegevenstabel

Ambitieus als Sara is, gaat ze voor 5 copiers: “Het lukt me wel om gemiddeld 2000 kopietjes per dag aan de man te brengen! Mijn winst verdubbelt dan tov 4 copiers.”

Een vriend zegt haar, dat ze dan wel een risico-analyse moet uitvoeren. Exact 2000 kopieën per dag is niet reëel: “Ik denk dat het aantal normaal verdeeld is met een gemiddelde van 2000 en een standaard-deviatie van 100.”

wa12In het tabblad WA3 van het Voorbeeldbestand is het model uitgebreid met een kans dat het betreffende aantal kopieën, op basis van het gemiddelde en SD , gehaald zal worden. Daarvoor gebruiken we in cel J11 de formule
=NORM.VERD.N(K11;$K$4;$K$5;ONWAAR)

Ofwel hoe groot is de kans dat er 1550 (cel K11) kopieën worden gemaakt onder de aanname dat de verdeling van het aantal Normaal Verdeeld is met een gemiddelde van 2000 (cel K4) en een standaard-deviatie van 100 (cel K5)? De echte kans, niet een cumulatieve (dus de laatste parameter is Onwaar).

Cel K10 bevat nu geen verwijzing naar de Winst maar naar de Winst * Kans.

Onder de gegevenstabel tellen we de, met de kansen,  gewogen winsten per kolom op. Die delen we door de som van de gebruikte kansen en krijgen dan de gemiddelde winst per kolom. De verwachte winst voor Sara wordt dan ongeveer 10% lager.

Multi-variabele Gegevenstabel

wa13Sara heeft het idee, dat ook de huurprijs nog bespreekbaar is en wil in de analyse hier rekening mee houden. Het model krijgt dan een derde input-variabele. Maar de Excel-gegevenstabel kan maar 2 dimensies aan.

Geen nood:

  1. zorg dat alle combinaties van de variabelen (dat kunnen er dus ook 10 zijn!), die doorgerekend moeten worden in een scenario-tabel zijn opgenomen
  2. wa14wijzig de input-tabel zodanig dat de input-variabelen afhankelijk zijn van het scenarionummer. Dat kan met Verticaal Zoeken in de scenariotabel (in het voorbeeld op het tabblad WA4 is dat B25:F37).
  3. maak een 1-dimensionale Gegevenstabel, waarbij in de eerste kolom de scenarionummers staan. In de kolomkoppen daarnaast maken we dan verwijzingen naar de scenario-tabel (zie hieronder de formule in cel K5) en naar de bijbehorende winst.
  4. wa15het is niet fraai, dat het resultaat van die verwijzingen altijd zichtbaar is: wijzig de opmaak van die kopjes zodanig, dat de tekstkleur gelijk is aan de achtergrondkleur.

Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: