1. ## 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. ## Re: LINKING AND ROUNDING (xp)

=int(sheet1!A1/1000)

Steve

3. ## 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. ## Re: LINKING AND ROUNDING (xp)

=int(Sheet1!A1/1000+0.5)

Steve

5. ## 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>

6. ## Re: LINKING AND ROUNDING (xp)

=ROUND(Sheet1!A1,-3)

7. ## Re: LINKING AND ROUNDING (xp)

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

8. ## 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.

9. ## 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>

#### Posting Permissions

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