Is it reliable to rely about what value we can see in the cell? Andif not, did I found the bug or wh

[MIUSS]

Hello,

I'll describe my problem:

I got a cell AE13 where we can see the value 15.

And in another cell I got this function:
=IF(AE13=15;AE13;IF(AE13=30;AE13;IF(AE13=45;AE13;0)))
So, I think this is as bright as it can be and in this cell we should
see the number 15. Unfortunately, the function returns zero value
instead of 15 as it should. How is it possible?

In addition I have to say, in the cell AE13, the number 15 is also a
returned value of another function, when I type directly 15 to this
cell, then it's all right and my function returns 15.

Please can you tell me where the fault can be?

Thank you very much in advance for any help.

Have a nice day!

 

[Bernard Liengme]

There can be a difference between the STORED number and the DISPLAYED number
A stored value of 15.0001 might be displayed with 4 decimals as 15.0001,
with 3 decimals as 15.000, .... or as 15

Next we have the problem of how computers store numbers in binary but we
work in decimal. You know that 1/3 is 0.33333333...... for ever. So there is
no way in decimal notation to exactly write one-third. Similarly, some
numbers (0.1 is an example) cannot be exactly represented in binary given
the finite number of digits stored. This leads to rounding errors. You might
expect 15 but you could get 14.99999999999782. and a test for "is this 15"
will fail.

SOLUTION:

Unless you are working with integers, never test for exact values without
rounding
=IF(ROUND(AE13, 12)=15 ..........

More details about EEE 754
Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113/en-us
(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/kb/42980
What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm
Visual Basic and Arithmetic Precision
http://support.microsoft.com/defaul...port/kb/articles/Q279/7/55.ASP&NoWebContent=1


best wishes

 

[MIUSS]

There can be a difference between the STORED number and the DISPLAYED number
A stored value of 15.0001 might be displayed with 4 decimals as 15.0001,
with 3 decimals as 15.000, .... or as 15

Next we have the problem of how computers store numbers in binary but we
work in decimal. You know that 1/3 is 0.33333333...... for ever. So there is
no way in decimal notation to exactly write one-third. Similarly, some
numbers (0.1 is an example) cannot be exactly represented in binary given
the finite number of digits stored. This leads to rounding errors. You might
expect 15 but you could get 14.99999999999782. and a test for "is this 15"
will fail.

SOLUTION:

Unless you are working with integers, never test for exact values without
rounding
=IF(ROUND(AE13, 12)=15  ..........

More details  about EEE 754
Floating-point arithmetic may give inaccurate results in Excelhttp://support.microsoft.com/kb/78113/en-us
(Complete) Tutorial to Understand IEEE Floating-Point Errorshttp://support..microsoft.com/kb/42980
What Every Computer Scientist Should Know About Floating Pointhttp://docs.sun.com/source/806-3568/ncg_goldberg.htmlhttp://www.cpearson.com/excel/rounding.htm
Visual Basic and Arithmetic Precisionhttp://support.microsoft.com/default.aspx?scid=http://support.microso...

best wishes

--
Bernard Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme













- Zobrazit citovaný text -

Hi,

thank you very much my friend, this helps at all. I ain't frequent
worker with excel tabs and this information is valuable for me, now
it's also working.

Have a nice day!

 

[Jerry W. Lewis]

You cannot rely on just what you see. Excel displays at most 15 significant
digits regardless of how many you request in the format. However the
internal representation (IEEE standard double precision) requires 17
significant digits to uniquely identify the stored value.

In general, you should assume that digits beyond the 15th may be different
than you expect, and that difference may be revealed by subtraction that
cancels some of the leading digits. If you want to know the exact value in a
given cell, use the D2D function that I posted at
http://groups.google.com/group/microsoft.public.excel/msg/6efb95785d1eaff5

Jerry