If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Rounding Question
Does anyone know if the format function ("Format/Number with x number of
decimal places) happens to conform with ASTM E29 for the last right-hand digit...??? Thanks Paul |
#2
|
|||
|
|||
Rounding Question
On Thu, 2 Feb 2006 06:56:55 -0800, wrote:
Does anyone know if the format function ("Format/Number with x number of decimal places) happens to conform with ASTM E29 for the last right-hand digit...??? Thanks Paul Format function? Are you talking about the VBA Format function? If so, it does not, as I understand you. The Format function returns a string of digits rounded according to the format expression. The rounding is done in the same method as the Format Cells dialog in Excel. Of course, the Format function actually changes the value. Whereas the Format Cells dialog only changes how the value is displayed. The VBA Round Function in VBA6+ does conform to the standard, as far as I know. It rounds the midway numbers to the nearest even number. --ron |
#3
|
|||
|
|||
Rounding Question
Hi Paul,
A simple experiment will show you that 1.575 rounds to 1.58 while 1.585 round to 1.59 with formatting and with the ROUND function. Clearly Excel does not use the ASTM E29 protocol (aka Banker's Rounding) However, VBA does follow ASTM E29 (see http://support.microsoft.com/default...;EN-GB;q194983 and http://support.microsoft.com/default...b;EN-US;196652) So the user-defined function below will round both 1.575 and 1.585 to 1.58 Function myround(rng, fig) myround = Round(rng, fig)End Functionbest wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ... Does anyone know if the format function ("Format/Number with x number of decimal places) happens to conform with ASTM E29 for the last right-hand digit...??? Thanks Paul |
#4
|
|||
|
|||
Rounding Question
VBA's Round function does not handle the vaguaries of binary approximations
very well, for instance myround(1110*0.0865,2) will round down instead of up. Your code will often produce more satisfactory results if you use Round(CDbl(CStr(rng)), fig) Also, VBA's Round function does not support negative arguments in the way that the worksheet function does. The code I posted at http://groups.google.com/group/micro...7fce6145b70d69 deals with this shortcoming. Does anyone know of any instances where bankers have EVER rounded in this way? Barring that, does anyone know how this came to be called "Banker's Rounding"? Jerry "Bernard Liengme" wrote: Hi Paul, A simple experiment will show you that 1.575 rounds to 1.58 while 1.585 round to 1.59 with formatting and with the ROUND function. Clearly Excel does not use the ASTM E29 protocol (aka Banker's Rounding) However, VBA does follow ASTM E29 (see http://support.microsoft.com/default...;EN-GB;q194983 and http://support.microsoft.com/default...b;EN-US;196652) So the user-defined function below will round both 1.575 and 1.585 to 1.58 Function myround(rng, fig) myround = Round(rng, fig)End Functionbest wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ... Does anyone know if the format function ("Format/Number with x number of decimal places) happens to conform with ASTM E29 for the last right-hand digit...??? Thanks Paul |
#5
|
|||
|
|||
Rounding Question
Many thanks, Jerry
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jerry W. Lewis" wrote in message ... VBA's Round function does not handle the vaguaries of binary approximations very well, for instance myround(1110*0.0865,2) will round down instead of up. Your code will often produce more satisfactory results if you use Round(CDbl(CStr(rng)), fig) Also, VBA's Round function does not support negative arguments in the way that the worksheet function does. The code I posted at http://groups.google.com/group/micro...7fce6145b70d69 deals with this shortcoming. Does anyone know of any instances where bankers have EVER rounded in this way? Barring that, does anyone know how this came to be called "Banker's Rounding"? Jerry "Bernard Liengme" wrote: Hi Paul, A simple experiment will show you that 1.575 rounds to 1.58 while 1.585 round to 1.59 with formatting and with the ROUND function. Clearly Excel does not use the ASTM E29 protocol (aka Banker's Rounding) However, VBA does follow ASTM E29 (see http://support.microsoft.com/default...;EN-GB;q194983 and http://support.microsoft.com/default...b;EN-US;196652) So the user-defined function below will round both 1.575 and 1.585 to 1.58 Function myround(rng, fig) myround = Round(rng, fig)End Functionbest wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message ... Does anyone know if the format function ("Format/Number with x number of decimal places) happens to conform with ASTM E29 for the last right-hand digit...??? Thanks Paul |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Question about reducing number of tables in a database | tlyczko | Database Design | 0 | October 27th, 2005 04:15 PM |
Rounding Data Question | sheabones | Worksheet Functions | 1 | September 29th, 2005 08:20 PM |
Search my question lost in the long list | Shrikant | General Discussion | 3 | August 26th, 2005 09:32 AM |
How to gray-out a question dialog box | S_Kaplan | General Discussion | 3 | October 26th, 2004 12:11 AM |
Designing a question paper | Ken | New Users | 2 | April 28th, 2004 10:13 PM |