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

=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

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

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

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)

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

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

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)

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

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

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.

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

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.