PC Review


Reply
Thread Tools Rate Thread

ROUNDING IN EXCEL

 
 
Anon
Guest
Posts: n/a
 
      13th Mar 2008
I have a customer who wants calculations Rounded per the requirements of
ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
numbers are rounded?

Thanks
Paul


 
Reply With Quote
 
 
 
 
Jim Thomlinson
Guest
Posts: n/a
 
      14th Mar 2008
Check out this link...

http://exceltips.vitalnews.com/Pages..._Take_Two.html
--
HTH...

Jim Thomlinson


"Anon" wrote:

> I have a customer who wants calculations Rounded per the requirements of
> ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
> numbers are rounded?
>
> Thanks
> Paul
>
>
>

 
Reply With Quote
 
 
 
 
Tyro
Guest
Posts: n/a
 
      14th Mar 2008
Have you Googled ASTM 29 Rounding? Excel is capable of rounding in
different ways. I'm sure you can find one that is suitable to meed the
standard

Tyro

"Anon" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have a customer who wants calculations Rounded per the requirements of
>ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
>numbers are rounded?
>
> Thanks
> Paul
>



 
Reply With Quote
 
BoniM
Guest
Posts: n/a
 
      14th Mar 2008
ASTM E29 - Rounding...
When the digit beyond the one you want to keep is less than 5, do not
change the digit you are keeping. When the digit beyond the one you want to
keep is greater than 5, increase the digit you are keeping by 1. When the
digit beyond the one you want to keep is equal to 5 and there are non-zero
digits beyond it, increase the digit you are keeping by 1. When the digit
beyond the one you want to keep is equal to 5 exactly, and the digit you
are keeping is odd, increase the digit you are keeping by 1. If the digit
you are keeping is even, keep it unchanged.

So, it doesn't... use this formula:
=IF(A2-INT(A2)-0.5=0,EVEN(ROUNDDOWN(A2,0)),ROUND(A2,0))
to round to whole numbers.

If you want decimal places... it gets more complicated.
Good luck!


"Anon" wrote:

> I have a customer who wants calculations Rounded per the requirements of
> ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
> numbers are rounded?
>
> Thanks
> Paul
>
>
>

 
Reply With Quote
 
Ron Rosenfeld
Guest
Posts: n/a
 
      14th Mar 2008
On Thu, 13 Mar 2008 15:51:41 -0700, "Anon" <(E-Mail Removed)> wrote:

>I have a customer who wants calculations Rounded per the requirements of
>ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
>numbers are rounded?
>
>Thanks
>Paul
>


Excel does not, but I believe VBA does. If it does, then you could use this
UDF:

To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter the formula

=bRound(num, numdigits)

into some cell where num is either the number you want to round, or a cell
reference containing that number; numdigits is the number of decimals to round
to (0 by default).

=========================
Function bRound(num As Double, Optional numdigits As Long = 0) As Double
bRound = Round(num, numdigits)
End Function
==========================================

--ron
 
Reply With Quote
 
Anon
Guest
Posts: n/a
 
      17th Mar 2008
Thanks everyone- lots to study!

Thanks again!


"Ron Rosenfeld" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Thu, 13 Mar 2008 15:51:41 -0700, "Anon" <(E-Mail Removed)> wrote:
>
>>I have a customer who wants calculations Rounded per the requirements of
>>ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
>>numbers are rounded?
>>
>>Thanks
>>Paul
>>

>
> Excel does not, but I believe VBA does. If it does, then you could use
> this
> UDF:
>
> To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
> highlighted in the Project Explorer window, then Insert/Module and paste
> the
> code below into the window that opens.
>
> To use this, enter the formula
>
> =bRound(num, numdigits)
>
> into some cell where num is either the number you want to round, or a cell
> reference containing that number; numdigits is the number of decimals to
> round
> to (0 by default).
>
> =========================
> Function bRound(num As Double, Optional numdigits As Long = 0) As Double
> bRound = Round(num, numdigits)
> End Function
> ==========================================
>
> --ron



 
Reply With Quote
 
Jerry W. Lewis
Guest
Posts: n/a
 
      19th Mar 2008
I think you will get more consistent results from the code I published at
http://groups.google.com/group/micro...7fce6145b70d69

Things my code fix are
- VBA Round function does not support rounding to negative digits
(multiples of 10)
- VBA Round function does not handle slight discrepancies in the binary
value, e.g. 1110*00.865 = 96.015, but =bRound(1110*00.865,2) returns 96.01
instead of 96.02

As a historical question, does anyone have evidence that ASTM rounding has
ever been a standard in banking? Barring that, does anyone know how this
came to be called "banker's rounding" in some circles?

Jerry

"Ron Rosenfeld" wrote:

> On Thu, 13 Mar 2008 15:51:41 -0700, "Anon" <(E-Mail Removed)> wrote:
>
> >I have a customer who wants calculations Rounded per the requirements of
> >ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
> >numbers are rounded?
> >
> >Thanks
> >Paul
> >

>
> Excel does not, but I believe VBA does. If it does, then you could use this
> UDF:
>
> To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
> highlighted in the Project Explorer window, then Insert/Module and paste the
> code below into the window that opens.
>
> To use this, enter the formula
>
> =bRound(num, numdigits)
>
> into some cell where num is either the number you want to round, or a cell
> reference containing that number; numdigits is the number of decimals to round
> to (0 by default).
>
> =========================
> Function bRound(num As Double, Optional numdigits As Long = 0) As Double
> bRound = Round(num, numdigits)
> End Function
> ==========================================
>
> --ron
>

 
Reply With Quote
 
Jerry W. Lewis
Guest
Posts: n/a
 
      19th Mar 2008
The information is somewhat dated. While rounding ties to an even rounded
number remains the standard from ASTM and most other (non-financial)
standards bodies that explicitly specify how to round, ANSI Z25.1 was
withdrawn so long ago that the ANSI bookstore still has not been able to tell
me when, despite having had a couple of days to research it.

Jerry

"Jim Thomlinson" wrote:

> Check out this link...
>
> http://exceltips.vitalnews.com/Pages..._Take_Two.html
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Anon" wrote:
>
> > I have a customer who wants calculations Rounded per the requirements of
> > ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29 when
> > numbers are rounded?
> >
> > Thanks
> > Paul
> >
> >
> >

 
Reply With Quote
 
Ron Rosenfeld
Guest
Posts: n/a
 
      19th Mar 2008
On Wed, 19 Mar 2008 01:42:01 -0700, Jerry W. Lewis <post_a_reply@no_e-mail.com>
wrote:

>I think you will get more consistent results from the code I published at
>http://groups.google.com/group/micro...7fce6145b70d69
>
>Things my code fix are
> - VBA Round function does not support rounding to negative digits
>(multiples of 10)
> - VBA Round function does not handle slight discrepancies in the binary
>value, e.g. 1110*00.865 = 96.015, but =bRound(1110*00.865,2) returns 96.01
>instead of 96.02
>
>As a historical question, does anyone have evidence that ASTM rounding has
>ever been a standard in banking? Barring that, does anyone know how this
>came to be called "banker's rounding" in some circles?
>
>Jerry


Thanks for adding that.
--ron
 
Reply With Quote
 
Ron Coderre
Guest
Posts: n/a
 
      19th Mar 2008
Per wikipedia:
"The origin of the term bankers' rounding is more obscure. If this rounding
method was ever a standard in banking, the evidence has proved extremely
difficult to find. To the contrary, section 2 of the European Commission
report 'The Introduction of the Euro and the Rounding of Currency Amounts'
suggests that there had previously been no standard approach to rounding in
banking."

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:(E-Mail Removed)...
>I think you will get more consistent results from the code I published at
> http://groups.google.com/group/micro...7fce6145b70d69
>
> Things my code fix are
> - VBA Round function does not support rounding to negative digits
> (multiples of 10)
> - VBA Round function does not handle slight discrepancies in the binary
> value, e.g. 1110*00.865 = 96.015, but =bRound(1110*00.865,2) returns 96.01
> instead of 96.02
>
> As a historical question, does anyone have evidence that ASTM rounding has
> ever been a standard in banking? Barring that, does anyone know how this
> came to be called "banker's rounding" in some circles?
>
> Jerry
>
> "Ron Rosenfeld" wrote:
>
>> On Thu, 13 Mar 2008 15:51:41 -0700, "Anon" <(E-Mail Removed)> wrote:
>>
>> >I have a customer who wants calculations Rounded per the requirements of
>> >ASTM E29. Does anyone know if Microsoft Excel complies with ASTM E29
>> >when
>> >numbers are rounded?
>> >
>> >Thanks
>> >Paul
>> >

>>
>> Excel does not, but I believe VBA does. If it does, then you could use
>> this
>> UDF:
>>
>> To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is
>> highlighted in the Project Explorer window, then Insert/Module and paste
>> the
>> code below into the window that opens.
>>
>> To use this, enter the formula
>>
>> =bRound(num, numdigits)
>>
>> into some cell where num is either the number you want to round, or a
>> cell
>> reference containing that number; numdigits is the number of decimals to
>> round
>> to (0 by default).
>>
>> =========================
>> Function bRound(num As Double, Optional numdigits As Long = 0) As Double
>> bRound = Round(num, numdigits)
>> End Function
>> ==========================================
>>
>> --ron
>>



 
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
ROUNDING FORMULA =ROUND(B12/$B$10,1) ROUNDING TO HIGH SunshineinFt.Myers Microsoft Excel Worksheet Functions 7 5th Mar 2009 07:41 PM
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
Rounding or not rounding =?Utf-8?B?Q29ubmllIE1hcnRpbg==?= Microsoft Excel Worksheet Functions 6 10th Apr 2006 06:24 PM
Rounding problem (controlling word from excel) robert burger Microsoft Excel Programming 6 30th Aug 2004 07:25 PM
Excel and rounding numbers Mosher Microsoft Excel Discussion 3 16th Dec 2003 08:38 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:04 PM.