Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Rounding

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Excel
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there a way to round in Excel using the odd/even rule? For example,

    I want 114,500 to round to 114,000 but Excel rounds to 115,000.

    115,500 rounds to 116,000 which is ok.

    If anyone can help, it would be greatly appreciated.

    rboss@leitztooling.com

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Midlands, UK
    Posts
    217
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have a look at these 2 functions :

    CEILING - rounds up to the nearest no. of signifcance
    FLOOR - like CEILING but rounds down


  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,049
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    Try:

    =IF(ISEVEN(INT(A1/1000)),INT(A1/1000),ROUNDUP(A1/1000,0))*1000

    or

    =IF(ISNUMBER(A1),IF(ISEVEN(INT(A1/1000)),INT(A1/1000),ROUNDUP(A1/1000,0))*1000,"")

    Aladin

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you state a rule for what you want to happen?
    your example is contradictory. do you want anything ending in 000 to 500 to round down to nearest thousand and anything ending in 501 to 999 to round up?

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Rounding to the nearest even number (or in this case thousands)

    Try:

    =EVEN(B6/1000)*1000

    Adjust the formula to how many places you want to round to:

    If you want to round to hundreds, divide by 100 and then multiply by 100


  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    According to rboss
    "I want 114,500 to round to 114,000 but Excel rounds to 115,000.
    115,500 rounds to 116,000 which is ok. "

    which isn't rounding to the nearest even no. or rounding to the nearest ooo.
    I'm trying to understand why it's ok to round 115.5 to 116, but not ok to round 114.5 to 115

    we need to know this to give proper answer, don't we?

  7. #7
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    He/she wants to round to the nearest even thousand

  8. #8
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-24 05:23, rboss wrote:
    Is there a way to round in Excel using the odd/even rule? For example,

    I want 114,500 to round to 114,000 but Excel rounds to 115,000.

    115,500 rounds to 116,000 which is ok.
    =IF(MOD(B5,B6)< B6/2,FLOOR(B5,B6),CEILING(B5,B6))

    This is a generic formula to do what you want.The number to be rounded is in B5 and the number to be rounded to the nearest multiple is in B6, in your case 2000. It rounds to the nearest multiple of 2000 which will give you the nearest even thousands. Of course, if you won't be changing what you are rounding to, just replace B6 with 2000 in the formula.

    Or, the easiest way is =MROUND(B5,2000) which requires the Analysis ToolPak.



    [ This Message was edited by: Steve Hartman on 2002-04-24 12:15 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Boston, Mass
    Posts
    182
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Another flavor??

    =ISODD(INT(B8/1000))*MROUND(B8,1000)+ISEVEN(INT(B8/1000))*FLOOR(B8,1000)

    You would need the analysis toolpack add-in to use the mround feature.


  10. #10
    New Member
    Join Date
    Apr 2002
    Location
    Lawrence, KS
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use Microsoft Access! They use banker's rounding by default, which makes me mad. Hartman, I remember you from the Access-Programmers-UK board.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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