Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Function for Rounding of Number
I want to round of number for example: 1.5 = 1 1.6 = 2 What function should I use. Please help me. Thanks -- aries0070 ------------------------------------------------------------------------ aries0070's Profile: http://www.excelforum.com/member.php...o&userid=25077 View this thread: http://www.excelforum.com/showthread...hreadid=386182 |
#2
|
|||
|
|||
=round(a1)
******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "aries0070" wrote in message ... I want to round of number for example: 1.5 = 1 1.6 = 2 What function should I use. Please help me. Thanks -- aries0070 ------------------------------------------------------------------------ aries0070's Profile: http://www.excelforum.com/member.php...o&userid=25077 View this thread: http://www.excelforum.com/showthread...hreadid=386182 |
#3
|
|||
|
|||
One way:
=ROUND(A1,0)-(MOD(A1,1)=0.5) In article , aries0070 wrote: I want to round of number for example: 1.5 = 1 1.6 = 2 What function should I use. Please help me. Thanks |
#4
|
|||
|
|||
That will give a syntax error, since you didn't include the second
argument. Note that even =ROUND(A1,0) will give the wrong answer for the OP's requirements. =ROUND(1.5,0) = 2, not 1. In article , "Anne Troy" wrote: =round(a1) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "aries0070" wrote in message ... I want to round of number for example: 1.5 = 1 1.6 = 2 What function should I use. Please help me. Thanks -- aries0070 ------------------------------------------------------------------------ aries0070's Profile: http://www.excelforum.com/member.php...o&userid=25077 View this thread: http://www.excelforum.com/showthread...hreadid=386182 |
#5
|
|||
|
|||
Note: that works only for non-negative numbers. If you may be rounding
negative numbers using the same pattern, use =ROUND(A1,0)-SIGN(A1)*(MOD(A1,1)=0.5) In article , JE McGimpsey wrote: One way: =ROUND(A1,0)-(MOD(A1,1)=0.5) |
#6
|
|||
|
|||
Generally 1.4 is rounded rounded down and 1.5 and above is rounded up. =Round(a1,0) will behave as above. If you want to round down at 1.5 you need to apply as =Round(a1-1,0) Elioch -- eliochPosted from http://www.pcreview.co.uk/ newsgroup access |
#7
|
|||
|
|||
CORRECTION =Round(a1,0) will behave as above. If you want to round down at 1.5 you need to apply as =Round(a1-0.1,0) it ishould be minus 0.1 and not minus 1 as stated before. Sorry Elioch -- eliochPosted from http://www.pcreview.co.uk/ newsgroup access |
#8
|
|||
|
|||
Not really:
=ROUND(A1-1,0) will round 1.6 to 1, not 2. In article , elioch <elioch.1s0oq1@ wrote: If you want to round down at 1.5 you need to apply as =Round(a1-1,0) |
#9
|
|||
|
|||
Not always! Some people, when rounding a number 5 as the test digit will round to even value. 1.35 - 1.4 1.45 - 1.4 I have seen this called Banker's Rounding, New Math rounding, Australian rounding. Best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "elioch" <elioch.1s0oq1@ wrote in message ... Generally 1.4 is rounded rounded down and 1.5 and above is rounded up. =Round(a1,0) will behave as above. If you want to round down at 1.5 you need to apply as =Round(a1-1,0) Elioch -- eliochPosted from http://www.pcreview.co.uk/ newsgroup access |
#10
|
|||
|
|||
Thanks. I blew that one, completely. Tried to get back to it when I saw they
wanted to round DOWN on .5, and got tied up. Thanks. :) "JE McGimpsey" wrote in message ... That will give a syntax error, since you didn't include the second argument. Note that even =ROUND(A1,0) will give the wrong answer for the OP's requirements. =ROUND(1.5,0) = 2, not 1. In article , "Anne Troy" wrote: =round(a1) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "aries0070" wrote in message ... I want to round of number for example: 1.5 = 1 1.6 = 2 What function should I use. Please help me. Thanks -- aries0070 ------------------------------------------------------------------------ aries0070's Profile: http://www.excelforum.com/member.php...o&userid=25077 View this thread: http://www.excelforum.com/showthread...hreadid=386182 |
#11
|
|||
|
|||
That type of rounding is very common in science, too, where it prevents
a bias away from zero. There are many other flavors. See http://support.microsoft.com/default...b;en-us;196652 In article , "Bernard Liengme" wrote: I have seen this called Banker's Rounding, New Math rounding, Australian rounding. |
#12
|
|||
|
|||
Thanks to all. I got it. -- aries0070 ------------------------------------------------------------------------ aries0070's Profile: http://www.excelforum.com/member.php...o&userid=25077 View this thread: http://www.excelforum.com/showthread...hreadid=386182 |
#13
|
|||
|
|||
A more bullet-proof approach is given in
http://groups-beta.google.com/group/...7fce6145b70d69 As an example of the difference, try rounding =1110*0.0865 to 2 decimal places with microsoft's bround function and my ASTMround. Microsoft's bround will round 96.0150000000000 to 96.01, where my ASTMround will correctly round it to 96.02. Another example of the difference is referenced in that post. Jerry JE McGimpsey wrote: That type of rounding is very common in science, too, where it prevents a bias away from zero. There are many other flavors. See http://support.microsoft.com/default...b;en-us;196652 In article , "Bernard Liengme" wrote: I have seen this called Banker's Rounding, New Math rounding, Australian rounding. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function to Count Number of Consecutive Rows with a Specific Criteria? | Excel Worksheet Functions | |||
Round a number in nested function | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Conversion | Excel Worksheet Functions | |||
The number change from 1350 to 13.5 and Enter does not function properly | Excel Discussion (Misc queries) |