cellen/formule kopiëren geeft niet juiste oplossing

[dannyvanoudenburg]

Beste excellers,
Bij het opstellen van de jaarlijkse afrekening van appartementen, wat betreft de gasrekening, stuit ik op onjuistheden in excel!
In de bijlage zal u bemerken dat ik op een bepaalde plaats getallen/formules kopieër naar een nieuwe plaats. Dit geeft bij verdere berekeningen
onjuistheden (weliswaar na de komma). Wiskundig onjuist!
Wanneer ik diezelfde getallen niet kopieër maar gewoon zo invul dan is de oplossing wel juist.
Toch wil ik deze kopieërfunctie blijven gebruiken. Wie weet hier raad?
Kan dit te maken hebben met relatieve/absolute verwijzingen ? Heb reeds uitgetest, zonder goed einde.
Help!

Danny
(zie bijlage)
Bekijk bijlage foute uitkomst bij kopiëren van formules.xlsx

 

[VenA]

De uitkomsten van excel zijn correct. Je zal even opzoek moeten gaan naar afronden.

 

[dannyvanoudenburg]

Met 'afronden' moet je gaan bepalen tot op hoeveel cijfers na de komma moet gerekend worden. In het tijdperk van de euro is twee cijfers na de komma
de te volgen trend meen ik. Alles moet juist blijven tot twee cijfers na de komma. Op mijn werkblad is dit niet zo, helaas.

 

[popipipo]

Bekijk dit een en bepaal zelf welke berekening volgens jou juist is: kolom G, I, of K

Afrondingen moet aan het eind van een berekening plaats vinden en niet telkens tussendoor.
Daar krijg je juist de 'fouten' door.

De 2121 is waarschijnlijk een afronding op hele euro's door de firma.
De door jou berekende euro per eenheid zal daarom waarschijnlijk anders zijn dan de firma hanteert.

 

[zapatr]

In het bestand van Danny heb ik van Excel geen fouten zien staan.
Maar los daarvan kan het wel voorkomen dat er met de bewerkingen die in het bestand worden gedaan, fouten ontstaan. Dat ligt aan het feit dat Excel binair rekent. Dergelijke fouten kun je voorkomen door een afrondingsfunctie te gebruiken (ook bij tussenberekeningen popipipo), bv. =afronden(berekening;4) als je op 4 decimalen wil afronden.

 

[dannyvanoudenburg]

Inderdaad, kolom K geeft het juiste eindresultaat van 2121 euro. Knap gevonden.
Maar nu ontstaat er een bijkomende vraag: moet functie "afronden" alleen op het einde van de berekening gebruikt worden of reeds bij tussenberekeningen?
Hoe kan ik zien op welk punt deze functie moet ingezet worden?

 

[zapatr]

Best reeds bij tussenberekeningen afronden, want anders kan het gebeuren dat je met ongewenste resultaten verder rekent waardoor er nog meer (of grotere) fouten ontstaan. Over het waarom kan een woordje uitleg wellicht verhelderend werken:
Neem bv. de breuk 2/3 , decimaal is dat 0,66666666...enz. Niet afgerond blijven die zessen zich herhalen. In het binaire talstelsel doet zich een vergelijkbaar probleem voor. Neem bv. 1/10 : decimaal is dat 0,1 maar binair wordt dat een reeks die zich blijft herhalen: 000110011001110011... Om dit te illustreren: typ in Excel in een willekeurige cel eens het volgende: =1*(0,3-0,2-0,1) . De uitkomst zou 0 moeten zijn (1 x 0 = 0), maar kijk wat er verschijnt. Dit is slechts 1 voorbeeld.
Om nu terug te keren naar je probleem: je weet niet vooraf welke getallen er bij de tussen- of eindberekeningen zullen verschijnen en dus ook niet of er problemen zoals hier vermeld zich zullen voordoen. Je kunt die problemen dus best vóór zijn door ook bij de tussenberekeningen (kolommen F en H) reeds af te ronden. Duidelijk zo?

 

[popipipo]

Best reeds bij tussen berekeningen afronden, want anders kan het gebeuren dat je met ongewenste resultaten verder rekent waardoor er nog meer (of grotere) fouten ontstaan.

Ik zie het net omgekeerd.

Door afronden creëer je juist fouten.
Neem het getal 5.555555
Afronden op 0 decimalen krijg je als uitkomst 6 een verschil van 0.44445
Afronden op 1 decimalen krijg je als uitkomst 5.6 een verschil van 0.04445
Afronden op 2 decimalen krijg je als uitkomst 5.56 een verschil van 0.00445
Afronden op 3 decimalen krijg je als uitkomst 5.556 een verschil van 0.00045

Dus hoe minder je afrond hoe nauwkeuriger je werkt
Als je vroeg in een berekening afrond dan neemt de fout door opstapelingen van afrondingsfouten alleen maar toe

Als je E16 tm E19 afrond op 3 getallen naar 2,259 dan krijg je dus 3* de afrondingsfout.
De som van G46:G19 komt juist 0.20 cent hoger uit dan in werkelijkheid.

 

[zapatr]

Ik denk dat we langs elkaar heen praten popipipo.
Het probleem dat door ts werd aangesneden behelst binaire fouten in Excel en hoe daar mee om te gaan teneinde aanvaardbare uitkomsten bij de berekeningen te krijgen. Waar jij het over hebt (afrondingen met decimale getallen) is wat anders. Als je mijn klein voorbeeldje hebt bekeken, dan zul je begrijpen dat uitgaande van een foute berekening je bij verdere bewerkingen met dat foute begin steeds grotere fouten krijgt. Stel je voor dat je
=1*(0,3-0,2-0,1) niet zou afronden op 0 maar met die eerste uitkomst verder zou rekenen.

 

[popipipo]

Het langs elkaar heen praten begrijp ik
Ik zie het probleem namelijk niet als een binaire probleem maar als een gewone afrondingsfout.

Maar laten wij niet in een welles nietes discussie raken en hopen dat danny begrijpt waarover we het hebben.
En dat hij het op zijn manier juist kan toepassen.

 

[dannyvanoudenburg]

Gezien de snelle reacties van popipipo, zapatr en VenA, is de oplossing niet zo direkt voor de handliggend zwart/wit.
Men kan op verschillende punten de functie "afronden" inzetten. Dit is hoogst belangrijk.
Ik begrijp het nut van deze formule nu maar al te goed, maar zal de gewone rekenmachine niet al te ver uit de buurt leggen.
Waarschijnlijk maken velen deze rekenfout in excel zonder het zelf te weten. Hopelijk is dit voor velen in de toekomst een aandachtspunt.
Hartelijk bedankt.