Foute formule of fout in Excel?

[R.E.]

Ik heb een formule in excel 2007 waarvan de uitkomst niet is wat ik verwacht:

code:

=IF(A1>B1;IF(1-((A1/B1-1)*10)<0;0;1-((A1/B1-1)*10));IF(1-((1-(A1/B1))*10)<0;0;1-((1-(A1/B1))*10)))

A1=45
B1=50

Zie hier de excel screenshot


en:


Er komt dus 2.22*10^-16 uit, terwijl het eigenlijk 0 zou moeten zijn. Wat gaat hier fout? Collega's en google hebben mij ook niet echt verder geholpen....

Wat gaat hier nou fout?

[indexample]

even heel snel gekeken:

in het begin: =IF(A1>B1 is false. is dat het niet?

[ Voor 12% gewijzigd door indexample op 28-05-2013 11:14 ]

[wouter12345]

Ik denk dat dit te maken heeft met floating point rounding errors in Microsoft Excel. Meer hierover lees je op How to correct rounding errors in floating-point arithmetic.

The IEEE 754 standard is a method of storing floating-point numbers in a compact way that is easy to manipulate. This standard is used by Intel coprocessors and most PC-based programs that implement floating-point math.

IEEE 754 specifies that numbers be stored in binary format to reduce storage requirements and allow the built-in binary arithmetic instructions that are available on all microprocessors to process the data in a relatively rapid fashion. However, some numbers that are simple, nonrepeating decimal numbers are converted into repeating binary numbers that cannot be stored with perfect accuracy.

For example, the number 1/10 can be represented in a decimal number system with a simple decimal:
.1
However, the same number in binary format becomes the repeating binary decimal:
.0001100011000111000111 (and so on)
This number cannot be represented in a finite amount of space. Therefore, this number is rounded down by approximately -2.78E-17 when it is stored.

[ Voor 69% gewijzigd door wouter12345 op 28-05-2013 11:23 ]

[Rannasha]

Heb geen directe oplossing voor je probleem, maar je kunt je formule sowieso eleganter opschrijven:

code:

IF( A1 > B1; MAX(0, 1 - ((A1 / B1 - 1) * 10); MAX(0, 1 - ((1 - (A1 / B1)) * 10) )

edit: MIN() moet natuurlijk MAX() zijn...

[ Voor 13% gewijzigd door Rannasha op 28-05-2013 11:24 ]

[R.E.]

@wouter12345: Als ik zo die link lees, is het inderdaad dat probleem.

@Rannasha: dit is inderdaad een stuk eleganter, zo had ik er nog niet over gedacht, maar geeft mij nog steeds hetzelfde resultaat

[SmiGueL]

Hier komt er 0 uit.
Toch 2.22E-16 , ik gebruikte 45 en 60

Wat ik bij die rounding errors altijd doe is:
- Cellen selecteren (A1 en B1 dus)
- 'Alles Wissen' die knop rechtsboven (incl opmaak)
- Opnieuw intypen

Het gaat bij mij altijd fout als je een formule naar beneden of rechts 'sleept' zodat hij steeds de voorgaande waarde pakt. Elke cel verder komt er ~0.00000000000001 bij en op een gegeven moment gaat dat dus fout..

[ Voor 54% gewijzigd door SmiGueL op 28-05-2013 11:35 ]

[Hero of Time]

Excel heeft niets met een Windows OS te maken, dus WOS > CSA.

[Rannasha]

Hier in LibreOffice komt er ook 0 uit. Het lijkt me inderdaad een roundoff error, want de uitkomst 2.22*10^-16 komt niet voor als mogelijke uitkomst van de IF-statement, want als je die handmatig evalueert zie je dat uitkomsten redelijk rond orde van grootte 1 moeten liggen met de gegeven input.

[R.E.]

@Hero: woeps, sorry bout that

@Rannasha: het is inderdaad de rounding error in de floating point. Toch vaag dat excel hier last van heeft, maar goed. Ik laat hem rounden op 5 cijfers achter de komma. Dat is voldoende voor mijn berekening.

[SmiGueL]

Als de invoerwaarden toch altijd gehele getallen zijn (of afgerond tot x cijfers achter de komma) dan werkt het ook om deze af te ronden tot x cijfers achter de komma. Er komt dan wel 0 uit en dan is het antwoord exact

[ Voor 8% gewijzigd door SmiGueL op 28-05-2013 11:44 ]