Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Houston, Texas, USA
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rounding integers to thousands (2000)

    Is there a way to round integers either up or down - whichever is traditionally appropriate - based on hundreds, thousands, etc.? For example, if we have a number such as $54,856,400.00, is there a function that would round this number to $54,900,000?

    Thanks in advance for your help!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rounding integers to thousands (2000)

    Say the non-rounded amount is in A1. In B1, enter the formula

    =ROUND(A1,-5)

    to round to the nearest multiple of 100,000.

    If you want to display thousands or millions (not tens or hundreds etc.), you can use a custom format in the cell itself: <code>#,##0,</code> will show thousands (54,856) and <code>#,##0,,</code> will show millions (55)

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Houston, Texas, USA
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rounding integers to thousands (2000)

    Sometimes the numbers I want to round are the result of adding two or more other cells together. Example, cell C1 displays 54,856,000 which results by adding A1 and B1 (=A1+B1). I want the result in C1 to be rounded, but when I use the formula you just indicated, the result shows up as "-" (sans the quotes). Why is this?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rounding integers to thousands (2000)

    The formula would be =ROUND(A1+B1,-5)

    Note: you should *not* combine such a formula with one of the custom formats I described - it's either a ROUND formula or a custom format, not both.

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Rounding integers to thousands (2000)

    If you don't mind using results that are expressed thousands without the 0s, then, instead of rounding off the number, you could use a custom number format.

    For example, if you use Format|Cells|Number|Custom and input:
    #,
    you'll get a result that's rounded off to the nearest thousand (ie: 123456789 becomes 123457) and
    [tab#,000,
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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