[Excel] Berekening Formule klopt niet.

[saxion]

Hallo,

in Excel heb ik de volgende formule:

=ALS(C1<>(A1-E1)-(F1-G1-H1);"xx";"")
=ALS(0<>(2,44-1,36)-(1,08-0-0);"xx";"")


De waarden zijn respectievelijk als volgt:

C1=0
A1=2,44
E1=1,36
F1=1,08
G1=0
H1=0

xx staat voor de waarde Waar.
<> staat voor ongelijk aan.

als uitkomst geeft Excel ook de waarde Waar, echter de uitkomst is Niet waar. Of heb ik het mis?

Als ik de formule valideer geeft Excel als uitkomst tussen =ALS(0<>(1,08)-(1,08) de volgende waarde aan: -2,222e-16 aan. Dit moet gewoon 0 zijn.


Hoe kan dit??

[TrailBlazer]

floating point berekening zijn altijd een beetje gammel howel ik het hier nog niet verwact had ff fkijken wat mijn flaptop doet.
Mijn laptop doet het wel goed. met de vergelijking die je geeft. Is de PC overgeclocked?

[ Voor 28% gewijzigd door TrailBlazer op 29-03-2006 09:59 ]

[Maasluip]

Dit moet een probleem met FP berekeningen zijn. Als je alle getallen x 100 doet krijg je wel het te verwachten resultaat. Ook als je de berekening in een apart veld doet en de ALS() op dat veld laat testen gaat het goed.
Bugje (it's a feature!) in Excel lijkt me.

[saxion]

mijn pc is niet overgeklokt..
zoiets simpels kan toch niet waar zijn.....een bugje..
trouwens ik gebruik Excel 2002 sp3

Hij moet dus niet XX in het veld zetten he? Doet hij dat?

[ Voor 34% gewijzigd door saxion op 29-03-2006 10:11 ]

[Janoz]

Dit is geen bug. Dit is gewoon gedocumenteerd gedrag van floating point types. Floating point getallen zijn altijd een benadering en aangezien hij er 2x10^-16 naast zit vind ik het toch pretty damn close. Dat is 0.000000000000001% van de rest.

[saxion]

toch vind ik het een raar verhaal over die fp. als ik een bv 0,5 -0,5 doe dan is dat toch gewoon 0?

[engelbertus]

en wat als je het nu andersom doet, dus
=ALS(C1=(A1-E1)-(F1-G1-H1);"";"xx")

[BtM909]

engelbertus schreef op woensdag 29 maart 2006 @ 13:27:
en wat als je het nu andersom doet, dus
=ALS(C1=(A1-E1)-(F1-G1-H1);"";"xx")

Als je het topic had gelezen dan had je ook al gezien dat het niks met de vergelijking an sich te maken heeft

[engelbertus]

dat is misschien zo, maar de onnauwkeurigheid moet toch blijkbaar ergens vandaan komen.
en een gewone = lijkt me eigenlijk simpeler dan een <> maar dat maakt misschien voor een computer niets uit, maar waarom maakt het dan wel uit dat er slechts 2 cijfers achter de komma staan.


bij wijze van spreken:
broertje van 5 kan al beter rekenen... waarom kan excel dit niet goed doen dan?
dit is zo willekeur...

hoe kan je dit als excell gebruiker ooit ondervangen?

[ Voor 47% gewijzigd door engelbertus op 29-03-2006 13:33 ]

[Janoz]

Ja klopt, maar dat komt omdat 0.5 als float erg simpel te representeren is. Hierbij treden geen afrondings fouten op. (Om precies te zijn is de mantisse overal 0 op de 'implied bit' na en de exponent staat op -1)

Voor 1.08 is dat echter een heel ander verhaal. Dit is niet mooi binair weer te geven. Vergelijk het met 1/3 dat niet exact in een fixed aantal decimalen weer te geven is.


Misschien kan ik het makkelijker voordoen in het 10-tallig stelsel met een significantie van 3:

dan rekenen we even de som 1/3 + 1/3 + 1/3 - 1 uit:

3333x10^-4 + 3333x10^-4 = 6666x10^-4
6666x10^-4 + 3333x10^-4 = 9999x10^-4
9999x10^-4 - 1x10⁰ = 1x10^-4

[saxion]

geeft gewoon hetzelfde resultaat. Het gaat erom dat hij als uitkomst in de formule 0,54-0,54 geen 0 geeft als resultaat

[Janoz]

Willekeur is het zeker niet. Het is keurig te voorspellen. Het is gewoon een kwestie van afrondings fouten mee calculeren. (Hiervoor zijn trouwens complete studies). Dit zou je op moeten lossen door niet rechtstreeks met 0 te vergelijken, maar door te kijken of de absolute afwijking buiten de significantie valt. Deze kleine waarde wordt over het algemeen epsilon genoemd en uit jouw voorbeeld blijkt wel dat je die zo rond de 1x10^-15 moet nemen.

[saxion]

Janoz schreef op woensdag 29 maart 2006 @ 13:32:
Ja klopt, maar dat komt omdat 0.5 als float erg simpel te representeren is. Hierbij treden geen afrondings fouten op. (Om precies te zijn is de mantisse overal 0 op de 'implied bit' na en de exponent staat op -1)

Voor 1.08 is dat echter een heel ander verhaal. Dit is niet mooi binair weer te geven. Vergelijk het met 1/3 dat niet exact in een fixed aantal decimalen weer te geven is.


Misschien kan ik het makkelijker voordoen in het 10-tallig stelsel met een significantie van 3:

dan rekenen we even de som 1/3 + 1/3 + 1/3 - 1 uit:

3333x10^-4 + 3333x10^-4 = 6666x10^-4
6666x10^-4 + 3333x10^-4 = 9999x10^-4
9999x10^-4 - 1x10⁰ = 1x10^-4

ok dankjewel, je verhaal is heel duidelijk!
is er nog een alternatief waarbij de uitkomst wel goed wordt weergeven misschien?

[Janoz]

Zie boven je post .. Meer info is trouwens hier te vinden.

[engelbertus]

dat kan ik wel begrijpen, maar er wordt niets gedeeld en er is dan toch ook geen onnauwkeurige uitkomst van 1/3
er is simpel het exactegetalen 1,08 bijvoorbeeld. dan maakt hettoch niet uit waar je de komma zet?of welke macht je het mee vermenigvuldigd?

waarom kan excel niet zelf ff op zijn vingers natellen, ipv afrondingen te gebruiken die er niet zijn, dat zijn binaire berekening niet deugt? als het een "gedocumenteerd gedrag is, dan is dat toch ook op te lossen?
het zou opgelost moeten worden omdat het nu gewoon fout is?

1,08 is binair toch niet een afronding?

[Janoz]

1.08 is binair wel degelijk een afronding.
1.1 is 1.5
1.01 is 1.25
1.001 is 1.125
1.0001 is 1.0625
1.00011 is 1.0625 + 0.003125 = 1.06575
1.000111 is 1.06575 + 0.0015625 = 1.0673125
1.0001111 is 1.0673125 + 0.00078125 = 1,06809375
1.00011111 is 1,068484375
1.000111111 is 1,0686796875


enz enz enz

Zoals je ziet gaat het nog een behoorlijk stuk door met de 1tjes voordat we uberhaupt bij de 1.08 aankomen. Ook zie je dat het verschil heel veel cijfers achter de komma heeft. Bij 10 significante bits is het verschil tussen de werkelijke en de opgeslagen waarden nog -0,0113203125.


Ik merk alweer dat ik een rekenfout gemaakt heb. Als ik het in excel doorreken (door 0.08 te benaderen zodat ik binnen de significantie blijf) kom ik als binaire representatie uit op:

code:

1,00000000000000000000 0 0
0,50000000000000000000 0 0
0,25000000000000000000 0 0
0,12500000000000000000 0 0
0,06250000000000000000 1 0,0625
0,03125000000000000000 0 0
0,01562500000000000000 1 0,015625
0,00781250000000000000 0 0
0,00390625000000000000 0 0
0,00195312500000000000 0 0
0,00097656250000000000 1 0,000976563
0,00048828125000000000 1 0,000488281
0,00024414062500000000 1 0,000244141
0,00012207031250000000 1 0,00012207
0,00006103515625000000 0 0
0,00003051757812500000 1 3,05176E-05
0,00001525878906250000 0 0
0,00000762939453125000 1 7,62939E-06
0,00000381469726562500 1 3,8147E-06
0,00000190734863281250 1 1,90735E-06
0,00000095367431640625 0 0
0,00000047683715820313 0 0
0,00000023841857910156 0 0
                         0,079999924

De binaire representatie van 1,08 met 24 bit significantie is: 1,00001010001111010111000 en dat is decimaal weer gelijk aan 1,079999924. Hier heb je dus al een fout van 0,000000076 te pakken.


Over het algemeen wordt bij 32 bits floating points getallen een presicie van 24 bits gebruikt. Gezien het verschil zou het best kunnen dat excel met 64 bits floats werkt.

[ Voor 64% gewijzigd door Janoz op 29-03-2006 14:27 ]

[Jimbolino]

saxion schreef op woensdag 29 maart 2006 @ 13:36:
[...]
ok dankjewel, je verhaal is heel duidelijk!
is er nog een alternatief waarbij de uitkomst wel goed wordt weergeven misschien?

Als je een tussenberekening maakt: =(A1-E1)-(F1-G1-H1)
en aan de hand van die uitkomst een logische test doet heb je geen last van het probleem

[Janoz]

Een tussenberekening gaat je probleem niet oplossen. Het enige wat gebeurt is dat er -2,222e-16 in een vakje wordt gezet.

[.oisyn]

@Janoz: je berekent een bit te weinig . Maakt voor het antwoord niets uit overigens, want 2^-23 is nog groter dan het verschil dat over is dus die staat ook op 0.

[saxion]

klopt had ik ook al geprobeerd. Gaat niet lukken helaas.

[El_kingo]

Je kunt dit wel oplossen door je (tussen)berekening te laten afronden op een bepaald aantal cijfers achter de komma m.b.v. ROUND() Dit is ook een van de standaard oplossingen die microsoft aandraagt...

[Dido]

Janoz schreef op woensdag 29 maart 2006 @ 14:26:
Een tussenberekening gaat je probleem niet oplossen. Het enige wat gebeurt is dat er -2,222e-16 in een vakje wordt gezet.

Gek, want bij mij (Excel2K) werkt het met een tussenresultaat uitstekend (tot mijn eigen verbazing - het lijkt of de standaard significantie van een cel minder is dan de suignificantie van een tussenresultaat in een berekening.)

[Janoz]

Misschien rekenen ze in Excel2K naast de waarde ook de significantie mee. Onderwater wordt een vergelijking dan uitgevoerd door te kijken of het verschil niet groter dan de berekende epsilon is.

[saxion]

sparcky schreef op woensdag 29 maart 2006 @ 15:02:
Je kunt dit wel oplossen door je (tussen)berekening te laten afronden op een bepaald aantal cijfers achter de komma m.b.v. ROUND() Dit is ook een van de standaard oplossingen die microsoft aandraagt...

hoe ziet de formule er dan precies uit?

[El_kingo]

Hmm, je hebt zeker de nederlandstalige versie van Excel?
Probeer in de help even te zoeken op afronden, dan komt ie wel boven drijven, staat ook precies uitgelegd hoe je de functie moet gebruiken...
Ik heb hier alleen een engelstalige versie...

[saxion]

round is dan afronden, moet ik snap nog steeds niet hoe die formule er dan uit zou moeten zien...

[PromoX]

Zoiets?
=ALS(C1<>AFRONDEN((A1-E1);2)-AFRONDEN((F1-G1-H1);2);"xx";"")

[saxion]

ja idd werkt! bedankt!