PC Review


Reply
Thread Tools Rate Thread

Need help implementing EPA rounding method

 
 
=?Utf-8?B?V2lsbCBTLg==?=
Guest
Posts: n/a
 
      7th Nov 2006
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.
 
Reply With Quote
 
 
 
 
Dana DeLouis
Guest
Posts: n/a
 
      7th Nov 2006
> ...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

--
HTH :>)
Dana DeLouis
Windows XP & Office 2003


"Will S." <Will (E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.



 
Reply With Quote
 
 
 
 
=?Utf-8?B?SmVycnkgVy4gTGV3aXM=?=
Guest
Posts: n/a
 
      7th Nov 2006
I gave a user defined function for the purpose at

http://groups.google.com/group/micro...7fce6145b70d69

This method is specified by ASTM and most other standards organizations that
choose to explicitly specify how rounding is done. How it came to be called
"bankers' rounding" in some computer circles is a mystery to me, since
bankers are one of the few groups who AFAIK never round in this way.

Jerry

"Will S." wrote:

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

 
Reply With Quote
 
=?Utf-8?B?V2lsbCBTLg==?=
Guest
Posts: n/a
 
      7th Nov 2006
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

"Jerry W. Lewis" wrote:

> I gave a user defined function for the purpose at
>
> http://groups.google.com/group/micro...7fce6145b70d69
>
> This method is specified by ASTM and most other standards organizations that
> choose to explicitly specify how rounding is done. How it came to be called
> "bankers' rounding" in some computer circles is a mystery to me, since
> bankers are one of the few groups who AFAIK never round in this way.
>
> Jerry
>
> "Will S." wrote:
>
> > 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.

 
Reply With Quote
 
=?Utf-8?B?RGF2ZSBG?=
Guest
Posts: n/a
 
      7th Nov 2006
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
--
Brevity is the soul of wit.


"Will S." wrote:

> 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
>
> "Jerry W. Lewis" wrote:
>
> > I gave a user defined function for the purpose at
> >
> > http://groups.google.com/group/micro...7fce6145b70d69
> >
> > This method is specified by ASTM and most other standards organizations that
> > choose to explicitly specify how rounding is done. How it came to be called
> > "bankers' rounding" in some computer circles is a mystery to me, since
> > bankers are one of the few groups who AFAIK never round in this way.
> >
> > Jerry
> >
> > "Will S." wrote:
> >
> > > 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.

 
Reply With Quote
 
Dana DeLouis
Guest
Posts: n/a
 
      7th Nov 2006
> ...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)))

--
Dana DeLouis
Windows XP & Office 2003


"Will S." <Will (E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.



 
Reply With Quote
 
bplumhoff@gmail.com
Guest
Posts: n/a
 
      7th Nov 2006
Hello,

This is from Jerry L. Lewis:
http://groups.google.com/group/micro...UTF-8&safe=off

Code:
Function RoundEven(num, Optional digits)
If IsMissing(digits) Then digits = 0
RoundEven = Round(CDbl(CStr(num)), digits)
End Function

HTH,
Bernd

 
Reply With Quote
 
=?Utf-8?B?UGFwYURvcw==?=
Guest
Posts: n/a
 
      8th Nov 2006
=ROUND( a1, 0 ) - AND( MOD( a1, 1 ) = 0.5, ISEVEN( a1 ) )
--
Regards,
Luc.

"Festina Lente"


"Will S." wrote:

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

 
Reply With Quote
 
joeu2004@hotmail.com
Guest
Posts: n/a
 
      8th Nov 2006
PapaDos wrote:
> =ROUND( a1, 0 ) - AND( MOD( a1, 1 ) = 0.5, ISEVEN( a1 ) )


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))

 
Reply With Quote
 
=?Utf-8?B?SmVycnkgVy4gTGV3aXM=?=
Guest
Posts: n/a
 
      8th Nov 2006
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

"(E-Mail Removed)" wrote:

> PapaDos wrote:
> > =ROUND( a1, 0 ) - AND( MOD( a1, 1 ) = 0.5, ISEVEN( a1 ) )

>
> 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))
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
I need a formula with rounding up & rounding down to the nearest . =?Utf-8?B?VG9ueSBLYXk=?= Microsoft Excel Worksheet Functions 3 29th May 2007 11:13 PM
Re: Implementing per-method trace Attribute class... Richard A. Lowe Microsoft C# .NET 0 22nd Jul 2004 06:56 PM
Alternate method of implementing cookieless sessions? =?Utf-8?B?ZGpsdWtlcg==?= Microsoft Dot NET 0 16th Jul 2004 10:48 AM
What's the point in implementing Reset() method of IEnumerator? Pavils Jurjans Microsoft C# .NET 2 28th May 2004 07:43 AM
Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! Help! -$- Windows XP Internet Explorer 2 21st Dec 2003 11:45 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:24 AM.