Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    LINKING AND ROUNDING (xp)

    I have a number in Worksheet 1 - cell a1. The number is 169200. I have it linked in Worksheet 2 - Cell A1 - =Sheet1!$A$1. How do I round this number in worksheet 2 to 169. I always want it to drop the last 3 digits and round down.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: LINKING AND ROUNDING (xp)

    =int(sheet1!A1/1000)

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LINKING AND ROUNDING (xp)

    ok - now they have decided that we should round up or down and truncate the last 3 digits. Therefore, 169200 should become 169 and 169550 should become 170.

    sorry! any ideas!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: LINKING AND ROUNDING (xp)

    =int(Sheet1!A1/1000+0.5)

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LINKING AND ROUNDING (xp)

    If you want the folks using it to have a chance at understanding the formula, you might want to use:

    <pre>=ROUND(Sheet1!$A$1/1000,0)
    </pre>

    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: LINKING AND ROUNDING (xp)

    =ROUND(Sheet1!A1,-3)
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LINKING AND ROUNDING (xp)

    John: That will give 169000, not the 169 that was wanted.
    Legare Coleman

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: LINKING AND ROUNDING (xp)

    Of course, you are correct.

    For the original poster, using the approaches above, =ROUND(Sheet1!A1,-3)/1000

    BUT to preserve the underlying number in the event it is to be used in further calculation, use this custom format (assuming you use the comma separator for thousands)
    _($* #,_);_($* (#,);_($* "-"??_);_(@_)
    or a variant if this is not currency data.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: LINKING AND ROUNDING (xp)

    Actually that's not too good either since dividing after the rounding could introduce additional rounding error. You really should use:

    <pre>=ROUND(Sheet1!A1/1000,0)
    </pre>

    Legare Coleman

Posting Permissions

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