Need help implementing EPA rounding method

W

Will S.

This question concerns the environmental field. The EPA recommends a method
of rounding which is statistically more accurate than the method we all
learned in elementary school. Basically, if the tenth digit is .5, you do
not simply round up, but rather round to the nearest EVEN whole number.

Ex:
1.5 = 2
10.5 = 10 (not 11)

The rule only makes a difference when the tenth digit is .5 . Using the IF
statement, I think I can create a spreadhseet function that evaluates a
number, and rounds to the even digit or rounds normally based on that
evaluation. In order for this to work, however, I need Excel to only
evaluate the first digit after the decimal point. The trouble is, I don't
know how to get Excel to do this.

I'm hoping that a simple manipulation of an existing function will solve
this since our network may kick out an attempt to use a macro.

Any help on this problem would be appreciated.
 
D

Dana DeLouis

...our network may kick out an attempt to use a macro.

That's too bad, as vba uses Bankers Rounding.

?WorksheetFunction.Round(10.5, 0)
11

?Round(10.5, 0)
10
 
W

Will S.

Jerry,

I'm glad to see that someone else knows what I'm talking about. It also
seems like the solution is only available in VB. Assuming I can make the
macro work under our security settings, how would I implement the solution
you've defined? First, where do I program it in. Second, how do I call it
up in the spreadsheet?

Thanks in advance,

-Will
 
D

Dave F

To get the code into the workbook:

1) Right click a tab on your workbook and select view code
2) On the lefthand side of the screen you will see a window which lists your
workbook, its associated objects, and any other workbook you have open.
Right-click on the workbook name in question, select Insert Module.
3) Copy and paste the code at the link into the large white space on the
right hand side of the screen.
4) Hit save.
5) Run the function like any other; i.e., =ASTMround(A1)
6) Post any questions you have including a detailed explanation of what you
have done so far.

As to your security settings--I have no idea how this would affect them,
because everyone's security situation is different.

Dave
 
D

Dana DeLouis

...our network may kick out an attempt to use a macro.

I know this is probably not foolproof, but just an initial attempt.
Mod can't work with very large numbers either.

=ROUND(A1,0)-(AND(MOD(A1,0.5)=0,ISEVEN(A1)))
 
J

joeu2004

PapaDos said:
=ROUND( a1, 0 ) - AND( MOD( a1, 1 ) = 0.5, ISEVEN( a1 ) )
Click to expand...

That was my answer. But to avoid the anomalous failure of MOD() with
large numbers, try:

=round(a1,0) - and(a1-int(a1)=0.5, iseven(a1))
 
J

Jerry W. Lewis

Your formula rounds in the wrong direction if A1<0.

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. As in my VBA function, you can treat
as equal to .5 any number that equals .5 to 15 figures by converting to a
string and back again. Thus

=ROUND(A1,0) - AND(A1-INT(VALUE(A1&""))=0.5, ISEVEN(A1))*SIGN(A1)

Should handle both issues.

Jerry
 
R

Ron Rosenfeld

This question concerns the environmental field. The EPA recommends a method
of rounding which is statistically more accurate than the method we all
learned in elementary school. Basically, if the tenth digit is .5, you do
not simply round up, but rather round to the nearest EVEN whole number.

Ex:
1.5 = 2
10.5 = 10 (not 11)

The rule only makes a difference when the tenth digit is .5 . Using the IF
statement, I think I can create a spreadhseet function that evaluates a
number, and rounds to the even digit or rounds normally based on that
evaluation. In order for this to work, however, I need Excel to only
evaluate the first digit after the decimal point. The trouble is, I don't
know how to get Excel to do this.

I'm hoping that a simple manipulation of an existing function will solve
this since our network may kick out an attempt to use a macro.

Any help on this problem would be appreciated.
Click to expand...


Perhaps:

=INT(A1+MOD(INT(A1),2)*MOD(A1,1))


--ron
 
J

joeu2004

Jerry said:
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.
Click to expand...

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.
 
J

Jerry W. Lewis

I might agree with you on not fuzzing to 15 figures if Excel allowed display
of 17 figures (required to uniquely identify a binary floating point value).
But given that the user is not permitted to see the differences that would
otherwise drive the rounding, coupled with the fact that the number to be
rounded is probably the result of a calculation (if you wanted the rounded
entry wouldn't you just enter the rounded value) and therefore unlikely to be
exactly .5 when true decimal calculations would be exactly .5, IMHO rounding
based on what you are permitted to see is more likely to be what users want
and expect from a rounding routine.

Jerry

[email protected] said:
Jerry said:
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.
Click to expand...

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.
Click to expand...
 
X

xlsuser42

If your system ends up kicking out a VBA code, try using this formula.
Copy and paste it into whatever cell you want. Assume that the numbe
you wish to round is in cell C4.

=IF(ROUND(C4,0)=ROUND(C4-0.1,0),ROUND(C4,0),IF(ISEVEN(ROUND(C4,0)),ROUND(C4,0),ROUNDDOWN(C4,0))
 
P

plb2882

That was my answer. But to avoid the anomalous failure of MOD() with
large numbers, try:

=round(a1,0) - and(a1-int(a1)=0.5, iseven(a1))
Click to expand...
This one works for positive numbers, when you get in the negative numbers it
rounds the wrong way.
 
P

plb2882

That was my answer. But to avoid the anomalous failure of MOD() with
large numbers, try:

=round(a1,0) - and(a1-int(a1)=0.5, iseven(a1))
Click to expand...
I was trying this out for negative numbers (loss) and it works well.
 
P

plb2882

plb2882 said:
This one works for positive numbers, when you get in the negative numbers
it rounds the wrong way.
Click to expand...
Sorry this one does work with negative numbers.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

rounding a number up to a certain ending digit 6
Round Function 5
Rounding Up question 2
Rounding 2
Formula help - rounding up and round down? 2
rounding problems 11
Currency and Rounding 0
Rounding problems when using excel 2

Top