View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
[email protected] joeu2004@hotmail.com is offline
external usenet poster
 
Posts: 418
Default Need help implementing EPA rounding method

Jerry W. Lewis wrote:
Also it may not give the intended result if A1 is calculated. There are 14
distinct binary numbers that all display as decimal 0.5, but your formula
will only recognize one of them as 0.5.


I realize that; in fact, it was intentional. It is not clear to me
whether the ASTM standard applies to "displayed" results or to actual
results. I cannot find a (free) online copy of the standard [1]. I
suspect the latter; that is, I suspect the ASTM standard makes no such
distinction, since the ASTM is not specific to any application (e.g.
Excel). In the real world, people need to decide at what point their
numbers should and should not be rounded. I suspect that the ASTM
standard specifies that all reported (i.e. visible) numbers and perhaps
all intermediate computed results are rounded according to the
standard.

Of course, the point you made in your article that you cited earlier is
that __other__ fractions ending in "5" (e.g. 0.05) cannot be
represented exactly in binary computers. Therefore, a simple
comparison with 0.05 (e.g.) might be suspicious. And that is where
your VBA function and the distinction between internal and displayed
representation becomes important. (Although one could argue that we
are splitting hairs.)

But the OP asked specifically about rounding 0.5 to an integer. Since
0.5 can be represented exactly in binary computers, I think a
comparison with 0.5 per se is valid and sufficient. Of course,
reasonable people can have differing opinions. That is simply mine.