+ Reply to Thread
Results 1 to 145 of 145

Function for Rounding of Number

  1. #1
    Registered User
    Join Date
    07-10-2005
    Posts
    5

    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

  2. #2
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    =round(a1)
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com


    "aries0070" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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. #3
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    One way:

    =ROUND(A1,0)-(MOD(A1,1)=0.5)

    In article <[email protected]>,
    aries0070 <[email protected]>
    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. #4
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Anne Troy" <[email protected]> wrote:

    > =round(a1)
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    > www.MyExpertsOnline.com
    >
    >
    > "aries0070" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > 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. #5
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    JE McGimpsey <[email protected]> wrote:

    > One way:
    >
    > =ROUND(A1,0)-(MOD(A1,1)=0.5)


  6. #6
    elioch
    Guest

    Re: Function for Rounding of Number


    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. #7
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    Not really:

    =ROUND(A1-1,0) will round 1.6 to 1, not 2.

    In article <[email protected]>,
    elioch <elioch.1s0oq1@> wrote:

    > If you want to round down at 1.5 you need to apply as
    >
    > =Round(a1-1,0)
    >


  8. #8
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  9. #9
    Bernard Liengme
    Guest

    Re: Function for Rounding of Number


    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
    news:[email protected]...
    >
    > 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. #10
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]>,
    > "Anne Troy" <[email protected]> wrote:
    >
    > > =round(a1)
    > > *******************
    > > ~Anne Troy
    > >
    > > www.OfficeArticles.com
    > > www.MyExpertsOnline.com
    > >
    > >
    > > "aries0070" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > 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. #11
    Registered User
    Join Date
    07-10-2005
    Posts
    5
    Thanks to all. I got it.

  12. #12
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Bernard Liengme" <[email protected]> wrote:

    > I have seen this called Banker's Rounding, New Math rounding, Australian
    > rounding.


  13. #13
    Jerry W. Lewis
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    > "Bernard Liengme" <[email protected]> wrote:
    >
    >
    >>I have seen this called Banker's Rounding, New Math rounding, Australian
    >>rounding.



  14. #14
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    =round(a1)
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com


    "aries0070" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  15. #15
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    One way:

    =ROUND(A1,0)-(MOD(A1,1)=0.5)

    In article <[email protected]>,
    aries0070 <[email protected]>
    wrote:

    > I want to round of number for example:
    >
    > 1.5 = 1
    > 1.6 = 2
    >
    > What function should I use. Please help me. Thanks


  16. #16
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Anne Troy" <[email protected]> wrote:

    > =round(a1)
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    > www.MyExpertsOnline.com
    >
    >
    > "aries0070" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > 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
    > >


  17. #17
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    JE McGimpsey <[email protected]> wrote:

    > One way:
    >
    > =ROUND(A1,0)-(MOD(A1,1)=0.5)


  18. #18
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  19. #19
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    Not really:

    =ROUND(A1-1,0) will round 1.6 to 1, not 2.

    In article <[email protected]>,
    elioch <elioch.1s0oq1@> wrote:

    > If you want to round down at 1.5 you need to apply as
    >
    > =Round(a1-1,0)
    >


  20. #20
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  21. #21
    Bernard Liengme
    Guest

    Re: Function for Rounding of Number


    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
    news:[email protected]...
    >
    > 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
    >




  22. #22
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]>,
    > "Anne Troy" <[email protected]> wrote:
    >
    > > =round(a1)
    > > *******************
    > > ~Anne Troy
    > >
    > > www.OfficeArticles.com
    > > www.MyExpertsOnline.com
    > >
    > >
    > > "aries0070" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > 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
    > > >




  23. #23
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Bernard Liengme" <[email protected]> wrote:

    > I have seen this called Banker's Rounding, New Math rounding, Australian
    > rounding.


  24. #24
    Jerry W. Lewis
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    > "Bernard Liengme" <[email protected]> wrote:
    >
    >
    >>I have seen this called Banker's Rounding, New Math rounding, Australian
    >>rounding.



  25. #25
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    =round(a1)
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com


    "aries0070" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  26. #26
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    One way:

    =ROUND(A1,0)-(MOD(A1,1)=0.5)

    In article <[email protected]>,
    aries0070 <[email protected]>
    wrote:

    > I want to round of number for example:
    >
    > 1.5 = 1
    > 1.6 = 2
    >
    > What function should I use. Please help me. Thanks


  27. #27
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Anne Troy" <[email protected]> wrote:

    > =round(a1)
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    > www.MyExpertsOnline.com
    >
    >
    > "aries0070" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > 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
    > >


  28. #28
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    JE McGimpsey <[email protected]> wrote:

    > One way:
    >
    > =ROUND(A1,0)-(MOD(A1,1)=0.5)


  29. #29
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  30. #30
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    Not really:

    =ROUND(A1-1,0) will round 1.6 to 1, not 2.

    In article <[email protected]>,
    elioch <elioch.1s0oq1@> wrote:

    > If you want to round down at 1.5 you need to apply as
    >
    > =Round(a1-1,0)
    >


  31. #31
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  32. #32
    Bernard Liengme
    Guest

    Re: Function for Rounding of Number


    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
    news:[email protected]...
    >
    > 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
    >




  33. #33
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]>,
    > "Anne Troy" <[email protected]> wrote:
    >
    > > =round(a1)
    > > *******************
    > > ~Anne Troy
    > >
    > > www.OfficeArticles.com
    > > www.MyExpertsOnline.com
    > >
    > >
    > > "aries0070" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > 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
    > > >




  34. #34
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Bernard Liengme" <[email protected]> wrote:

    > I have seen this called Banker's Rounding, New Math rounding, Australian
    > rounding.


  35. #35
    Jerry W. Lewis
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    > "Bernard Liengme" <[email protected]> wrote:
    >
    >
    >>I have seen this called Banker's Rounding, New Math rounding, Australian
    >>rounding.



  36. #36
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    =round(a1)
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com


    "aries0070" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  37. #37
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    One way:

    =ROUND(A1,0)-(MOD(A1,1)=0.5)

    In article <[email protected]>,
    aries0070 <[email protected]>
    wrote:

    > I want to round of number for example:
    >
    > 1.5 = 1
    > 1.6 = 2
    >
    > What function should I use. Please help me. Thanks


  38. #38
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Anne Troy" <[email protected]> wrote:

    > =round(a1)
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    > www.MyExpertsOnline.com
    >
    >
    > "aries0070" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > 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
    > >


  39. #39
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    JE McGimpsey <[email protected]> wrote:

    > One way:
    >
    > =ROUND(A1,0)-(MOD(A1,1)=0.5)


  40. #40
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  41. #41
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    Not really:

    =ROUND(A1-1,0) will round 1.6 to 1, not 2.

    In article <[email protected]>,
    elioch <elioch.1s0oq1@> wrote:

    > If you want to round down at 1.5 you need to apply as
    >
    > =Round(a1-1,0)
    >


  42. #42
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  43. #43
    Bernard Liengme
    Guest

    Re: Function for Rounding of Number


    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
    news:[email protected]...
    >
    > 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
    >




  44. #44
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]>,
    > "Anne Troy" <[email protected]> wrote:
    >
    > > =round(a1)
    > > *******************
    > > ~Anne Troy
    > >
    > > www.OfficeArticles.com
    > > www.MyExpertsOnline.com
    > >
    > >
    > > "aries0070" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > 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
    > > >




  45. #45
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Bernard Liengme" <[email protected]> wrote:

    > I have seen this called Banker's Rounding, New Math rounding, Australian
    > rounding.


  46. #46
    Jerry W. Lewis
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    > "Bernard Liengme" <[email protected]> wrote:
    >
    >
    >>I have seen this called Banker's Rounding, New Math rounding, Australian
    >>rounding.



  47. #47
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    =round(a1)
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com


    "aries0070" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  48. #48
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    One way:

    =ROUND(A1,0)-(MOD(A1,1)=0.5)

    In article <[email protected]>,
    aries0070 <[email protected]>
    wrote:

    > I want to round of number for example:
    >
    > 1.5 = 1
    > 1.6 = 2
    >
    > What function should I use. Please help me. Thanks


  49. #49
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Anne Troy" <[email protected]> wrote:

    > =round(a1)
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    > www.MyExpertsOnline.com
    >
    >
    > "aries0070" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > 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
    > >


  50. #50
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    JE McGimpsey <[email protected]> wrote:

    > One way:
    >
    > =ROUND(A1,0)-(MOD(A1,1)=0.5)


  51. #51
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  52. #52
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    Not really:

    =ROUND(A1-1,0) will round 1.6 to 1, not 2.

    In article <[email protected]>,
    elioch <elioch.1s0oq1@> wrote:

    > If you want to round down at 1.5 you need to apply as
    >
    > =Round(a1-1,0)
    >


  53. #53
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  54. #54
    Bernard Liengme
    Guest

    Re: Function for Rounding of Number


    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
    news:[email protected]...
    >
    > 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
    >




  55. #55
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]>,
    > "Anne Troy" <[email protected]> wrote:
    >
    > > =round(a1)
    > > *******************
    > > ~Anne Troy
    > >
    > > www.OfficeArticles.com
    > > www.MyExpertsOnline.com
    > >
    > >
    > > "aries0070" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > 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
    > > >




  56. #56
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Bernard Liengme" <[email protected]> wrote:

    > I have seen this called Banker's Rounding, New Math rounding, Australian
    > rounding.


  57. #57
    Jerry W. Lewis
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    > "Bernard Liengme" <[email protected]> wrote:
    >
    >
    >>I have seen this called Banker's Rounding, New Math rounding, Australian
    >>rounding.



  58. #58
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    =round(a1)
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com


    "aries0070" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  59. #59
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    One way:

    =ROUND(A1,0)-(MOD(A1,1)=0.5)

    In article <[email protected]>,
    aries0070 <[email protected]>
    wrote:

    > I want to round of number for example:
    >
    > 1.5 = 1
    > 1.6 = 2
    >
    > What function should I use. Please help me. Thanks


  60. #60
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Anne Troy" <[email protected]> wrote:

    > =round(a1)
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    > www.MyExpertsOnline.com
    >
    >
    > "aries0070" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > 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
    > >


  61. #61
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    JE McGimpsey <[email protected]> wrote:

    > One way:
    >
    > =ROUND(A1,0)-(MOD(A1,1)=0.5)


  62. #62
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  63. #63
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    Not really:

    =ROUND(A1-1,0) will round 1.6 to 1, not 2.

    In article <[email protected]>,
    elioch <elioch.1s0oq1@> wrote:

    > If you want to round down at 1.5 you need to apply as
    >
    > =Round(a1-1,0)
    >


  64. #64
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  65. #65
    Bernard Liengme
    Guest

    Re: Function for Rounding of Number


    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
    news:[email protected]...
    >
    > 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
    >




  66. #66
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]>,
    > "Anne Troy" <[email protected]> wrote:
    >
    > > =round(a1)
    > > *******************
    > > ~Anne Troy
    > >
    > > www.OfficeArticles.com
    > > www.MyExpertsOnline.com
    > >
    > >
    > > "aries0070" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > 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
    > > >




  67. #67
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Bernard Liengme" <[email protected]> wrote:

    > I have seen this called Banker's Rounding, New Math rounding, Australian
    > rounding.


  68. #68
    Jerry W. Lewis
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    > "Bernard Liengme" <[email protected]> wrote:
    >
    >
    >>I have seen this called Banker's Rounding, New Math rounding, Australian
    >>rounding.



  69. #69
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    =round(a1)
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com


    "aries0070" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  70. #70
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    One way:

    =ROUND(A1,0)-(MOD(A1,1)=0.5)

    In article <[email protected]>,
    aries0070 <[email protected]>
    wrote:

    > I want to round of number for example:
    >
    > 1.5 = 1
    > 1.6 = 2
    >
    > What function should I use. Please help me. Thanks


  71. #71
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Anne Troy" <[email protected]> wrote:

    > =round(a1)
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    > www.MyExpertsOnline.com
    >
    >
    > "aries0070" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > 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
    > >


  72. #72
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    JE McGimpsey <[email protected]> wrote:

    > One way:
    >
    > =ROUND(A1,0)-(MOD(A1,1)=0.5)


  73. #73
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  74. #74
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    Not really:

    =ROUND(A1-1,0) will round 1.6 to 1, not 2.

    In article <[email protected]>,
    elioch <elioch.1s0oq1@> wrote:

    > If you want to round down at 1.5 you need to apply as
    >
    > =Round(a1-1,0)
    >


  75. #75
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  76. #76
    Bernard Liengme
    Guest

    Re: Function for Rounding of Number


    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
    news:[email protected]...
    >
    > 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
    >




  77. #77
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]>,
    > "Anne Troy" <[email protected]> wrote:
    >
    > > =round(a1)
    > > *******************
    > > ~Anne Troy
    > >
    > > www.OfficeArticles.com
    > > www.MyExpertsOnline.com
    > >
    > >
    > > "aries0070" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > 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
    > > >




  78. #78
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Bernard Liengme" <[email protected]> wrote:

    > I have seen this called Banker's Rounding, New Math rounding, Australian
    > rounding.


  79. #79
    Jerry W. Lewis
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    > "Bernard Liengme" <[email protected]> wrote:
    >
    >
    >>I have seen this called Banker's Rounding, New Math rounding, Australian
    >>rounding.



  80. #80
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    =round(a1)
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com


    "aries0070" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  81. #81
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    One way:

    =ROUND(A1,0)-(MOD(A1,1)=0.5)

    In article <[email protected]>,
    aries0070 <[email protected]>
    wrote:

    > I want to round of number for example:
    >
    > 1.5 = 1
    > 1.6 = 2
    >
    > What function should I use. Please help me. Thanks


  82. #82
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Anne Troy" <[email protected]> wrote:

    > =round(a1)
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    > www.MyExpertsOnline.com
    >
    >
    > "aries0070" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > 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
    > >


  83. #83
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    JE McGimpsey <[email protected]> wrote:

    > One way:
    >
    > =ROUND(A1,0)-(MOD(A1,1)=0.5)


  84. #84
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  85. #85
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    Not really:

    =ROUND(A1-1,0) will round 1.6 to 1, not 2.

    In article <[email protected]>,
    elioch <elioch.1s0oq1@> wrote:

    > If you want to round down at 1.5 you need to apply as
    >
    > =Round(a1-1,0)
    >


  86. #86
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  87. #87
    Bernard Liengme
    Guest

    Re: Function for Rounding of Number


    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
    news:[email protected]...
    >
    > 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
    >




  88. #88
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]>,
    > "Anne Troy" <[email protected]> wrote:
    >
    > > =round(a1)
    > > *******************
    > > ~Anne Troy
    > >
    > > www.OfficeArticles.com
    > > www.MyExpertsOnline.com
    > >
    > >
    > > "aries0070" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > 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
    > > >




  89. #89
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Bernard Liengme" <[email protected]> wrote:

    > I have seen this called Banker's Rounding, New Math rounding, Australian
    > rounding.


  90. #90
    Jerry W. Lewis
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    > "Bernard Liengme" <[email protected]> wrote:
    >
    >
    >>I have seen this called Banker's Rounding, New Math rounding, Australian
    >>rounding.



  91. #91
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    =round(a1)
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com


    "aries0070" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  92. #92
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    One way:

    =ROUND(A1,0)-(MOD(A1,1)=0.5)

    In article <[email protected]>,
    aries0070 <[email protected]>
    wrote:

    > I want to round of number for example:
    >
    > 1.5 = 1
    > 1.6 = 2
    >
    > What function should I use. Please help me. Thanks


  93. #93
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Anne Troy" <[email protected]> wrote:

    > =round(a1)
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    > www.MyExpertsOnline.com
    >
    >
    > "aries0070" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > 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
    > >


  94. #94
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    JE McGimpsey <[email protected]> wrote:

    > One way:
    >
    > =ROUND(A1,0)-(MOD(A1,1)=0.5)


  95. #95
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  96. #96
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    Not really:

    =ROUND(A1-1,0) will round 1.6 to 1, not 2.

    In article <[email protected]>,
    elioch <elioch.1s0oq1@> wrote:

    > If you want to round down at 1.5 you need to apply as
    >
    > =Round(a1-1,0)
    >


  97. #97
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  98. #98
    Bernard Liengme
    Guest

    Re: Function for Rounding of Number


    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
    news:[email protected]...
    >
    > 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
    >




  99. #99
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]>,
    > "Anne Troy" <[email protected]> wrote:
    >
    > > =round(a1)
    > > *******************
    > > ~Anne Troy
    > >
    > > www.OfficeArticles.com
    > > www.MyExpertsOnline.com
    > >
    > >
    > > "aries0070" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > 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
    > > >




  100. #100
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Bernard Liengme" <[email protected]> wrote:

    > I have seen this called Banker's Rounding, New Math rounding, Australian
    > rounding.


  101. #101
    Jerry W. Lewis
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    > "Bernard Liengme" <[email protected]> wrote:
    >
    >
    >>I have seen this called Banker's Rounding, New Math rounding, Australian
    >>rounding.



  102. #102
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    =round(a1)
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com


    "aries0070" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  103. #103
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    One way:

    =ROUND(A1,0)-(MOD(A1,1)=0.5)

    In article <[email protected]>,
    aries0070 <[email protected]>
    wrote:

    > I want to round of number for example:
    >
    > 1.5 = 1
    > 1.6 = 2
    >
    > What function should I use. Please help me. Thanks


  104. #104
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Anne Troy" <[email protected]> wrote:

    > =round(a1)
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    > www.MyExpertsOnline.com
    >
    >
    > "aries0070" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > 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
    > >


  105. #105
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    JE McGimpsey <[email protected]> wrote:

    > One way:
    >
    > =ROUND(A1,0)-(MOD(A1,1)=0.5)


  106. #106
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  107. #107
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    Not really:

    =ROUND(A1-1,0) will round 1.6 to 1, not 2.

    In article <[email protected]>,
    elioch <elioch.1s0oq1@> wrote:

    > If you want to round down at 1.5 you need to apply as
    >
    > =Round(a1-1,0)
    >


  108. #108
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  109. #109
    Bernard Liengme
    Guest

    Re: Function for Rounding of Number


    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
    news:[email protected]...
    >
    > 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
    >




  110. #110
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]>,
    > "Anne Troy" <[email protected]> wrote:
    >
    > > =round(a1)
    > > *******************
    > > ~Anne Troy
    > >
    > > www.OfficeArticles.com
    > > www.MyExpertsOnline.com
    > >
    > >
    > > "aries0070" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > 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
    > > >




  111. #111
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Bernard Liengme" <[email protected]> wrote:

    > I have seen this called Banker's Rounding, New Math rounding, Australian
    > rounding.


  112. #112
    Jerry W. Lewis
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    > "Bernard Liengme" <[email protected]> wrote:
    >
    >
    >>I have seen this called Banker's Rounding, New Math rounding, Australian
    >>rounding.



  113. #113
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    =round(a1)
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com


    "aries0070" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  114. #114
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    One way:

    =ROUND(A1,0)-(MOD(A1,1)=0.5)

    In article <[email protected]>,
    aries0070 <[email protected]>
    wrote:

    > I want to round of number for example:
    >
    > 1.5 = 1
    > 1.6 = 2
    >
    > What function should I use. Please help me. Thanks


  115. #115
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Anne Troy" <[email protected]> wrote:

    > =round(a1)
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    > www.MyExpertsOnline.com
    >
    >
    > "aries0070" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > 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
    > >


  116. #116
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    JE McGimpsey <[email protected]> wrote:

    > One way:
    >
    > =ROUND(A1,0)-(MOD(A1,1)=0.5)


  117. #117
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  118. #118
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    Not really:

    =ROUND(A1-1,0) will round 1.6 to 1, not 2.

    In article <[email protected]>,
    elioch <elioch.1s0oq1@> wrote:

    > If you want to round down at 1.5 you need to apply as
    >
    > =Round(a1-1,0)
    >


  119. #119
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  120. #120
    Bernard Liengme
    Guest

    Re: Function for Rounding of Number


    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
    news:[email protected]...
    >
    > 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
    >




  121. #121
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]>,
    > "Anne Troy" <[email protected]> wrote:
    >
    > > =round(a1)
    > > *******************
    > > ~Anne Troy
    > >
    > > www.OfficeArticles.com
    > > www.MyExpertsOnline.com
    > >
    > >
    > > "aries0070" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > 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
    > > >




  122. #122
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Bernard Liengme" <[email protected]> wrote:

    > I have seen this called Banker's Rounding, New Math rounding, Australian
    > rounding.


  123. #123
    Jerry W. Lewis
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    > "Bernard Liengme" <[email protected]> wrote:
    >
    >
    >>I have seen this called Banker's Rounding, New Math rounding, Australian
    >>rounding.



  124. #124
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    =round(a1)
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com


    "aries0070" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  125. #125
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    One way:

    =ROUND(A1,0)-(MOD(A1,1)=0.5)

    In article <[email protected]>,
    aries0070 <[email protected]>
    wrote:

    > I want to round of number for example:
    >
    > 1.5 = 1
    > 1.6 = 2
    >
    > What function should I use. Please help me. Thanks


  126. #126
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Anne Troy" <[email protected]> wrote:

    > =round(a1)
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    > www.MyExpertsOnline.com
    >
    >
    > "aries0070" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > 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
    > >


  127. #127
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    JE McGimpsey <[email protected]> wrote:

    > One way:
    >
    > =ROUND(A1,0)-(MOD(A1,1)=0.5)


  128. #128
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  129. #129
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    Not really:

    =ROUND(A1-1,0) will round 1.6 to 1, not 2.

    In article <[email protected]>,
    elioch <elioch.1s0oq1@> wrote:

    > If you want to round down at 1.5 you need to apply as
    >
    > =Round(a1-1,0)
    >


  130. #130
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  131. #131
    Bernard Liengme
    Guest

    Re: Function for Rounding of Number


    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
    news:[email protected]...
    >
    > 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
    >




  132. #132
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]>,
    > "Anne Troy" <[email protected]> wrote:
    >
    > > =round(a1)
    > > *******************
    > > ~Anne Troy
    > >
    > > www.OfficeArticles.com
    > > www.MyExpertsOnline.com
    > >
    > >
    > > "aries0070" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > 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
    > > >




  133. #133
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Bernard Liengme" <[email protected]> wrote:

    > I have seen this called Banker's Rounding, New Math rounding, Australian
    > rounding.


  134. #134
    Jerry W. Lewis
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    > "Bernard Liengme" <[email protected]> wrote:
    >
    >
    >>I have seen this called Banker's Rounding, New Math rounding, Australian
    >>rounding.



  135. #135
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    =round(a1)
    *******************
    ~Anne Troy

    www.OfficeArticles.com
    www.MyExpertsOnline.com


    "aries0070" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  136. #136
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    One way:

    =ROUND(A1,0)-(MOD(A1,1)=0.5)

    In article <[email protected]>,
    aries0070 <[email protected]>
    wrote:

    > I want to round of number for example:
    >
    > 1.5 = 1
    > 1.6 = 2
    >
    > What function should I use. Please help me. Thanks


  137. #137
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Anne Troy" <[email protected]> wrote:

    > =round(a1)
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    > www.MyExpertsOnline.com
    >
    >
    > "aries0070" <[email protected]> wrote
    > in message news:[email protected]...
    > >
    > > 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
    > >


  138. #138
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    JE McGimpsey <[email protected]> wrote:

    > One way:
    >
    > =ROUND(A1,0)-(MOD(A1,1)=0.5)


  139. #139
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  140. #140
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    Not really:

    =ROUND(A1-1,0) will round 1.6 to 1, not 2.

    In article <[email protected]>,
    elioch <elioch.1s0oq1@> wrote:

    > If you want to round down at 1.5 you need to apply as
    >
    > =Round(a1-1,0)
    >


  141. #141
    elioch
    Guest

    Re: Function for Rounding of Number


    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


  142. #142
    Bernard Liengme
    Guest

    Re: Function for Rounding of Number


    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
    news:[email protected]...
    >
    > 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
    >




  143. #143
    Anne Troy
    Guest

    Re: Function for Rounding of Number

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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 <[email protected]>,
    > "Anne Troy" <[email protected]> wrote:
    >
    > > =round(a1)
    > > *******************
    > > ~Anne Troy
    > >
    > > www.OfficeArticles.com
    > > www.MyExpertsOnline.com
    > >
    > >
    > > "aries0070" <[email protected]>

    wrote
    > > in message

    news:[email protected]...
    > > >
    > > > 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
    > > >




  144. #144
    JE McGimpsey
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    "Bernard Liengme" <[email protected]> wrote:

    > I have seen this called Banker's Rounding, New Math rounding, Australian
    > rounding.


  145. #145
    Jerry W. Lewis
    Guest

    Re: Function for Rounding of Number

    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 <[email protected]>,
    > "Bernard Liengme" <[email protected]> wrote:
    >
    >
    >>I have seen this called Banker's Rounding, New Math rounding, Australian
    >>rounding.



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1