Formatting cells for automatic rounding 3

[roscok]

Hi all,

This should be simple, but I can't figure it out.

What I want to do is have the cells formatted so when I type in a number it is automatically rounded to thousands.

ie. Type in 10,234,987 and the cell result is 10,234,000

Thanks in advance

Ross

 

[Zathras]

You can assign a custom format to the cell (or column or row or worksheet). If you use:

#,##0,",000"

your entry of 10234987 will appear as 10,235,000

The cell contents will still be 10234987

I don't think there is a way to show it truncated as 10,234,000 as you indicated. Rounded is as close as you can get.

 

[roscok]

Thanks Zathras,

I knew it had to be simple.

Ross

 

[DaleWatson123321]

roscock,

With formatting, Zathras is right about not being able to truncate the "actual" value in the cell. It only affects how it's displayed.

However, if you should need to later truncate the "actual" value, you can do this by using a formula in another cell that references the original cell. The following formula will work...

=ROUND(A1,-3)

If the value in cell A1 is: 10,234,987 - then the above formula will produce an "actual" value of: 10,234,000

Hope this can be of use.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Zathras]

Ross, your post was a little ambiguous. You used the prhase "rounded to thousands", but your example illustrated truncation, not rounding.

So, if you want a rounded number (10,235,000), follow Dale's formula. However, if you really want truncation (10,234,000), you can use this variation:

   =TRUNC(A1,-3)

 

[DaleWatson123321]

Zathras,

Thanks for correcting me. It's getting late here. I had spoken of "truncating" and indeed meant to use =TRUNC(A1,-3) instead of =ROUND(A1,-3)

Thanks again, and another STAR for your help. ;-)

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[roscok]

Hey Guys,

It is Monday. When I went back to the user he informed me that he wanted to actually cut the last three digits and round.

I ended up using #,###, and this solved his problem.

Thanks for the help

Ross

 

[Zathras]

Please be sure you user understands that all that the format does is change the way the number looks. The cell contents retains all of the digits whether they are displayed or not.

Your last post is still ambiguous in its meaning. "actually cut the last three digits" conflicts with "and round" --- they are not the same thing.

For example, if you have three numbers and want to use a formula to compute the sum:

10,234,987
10,001,400
10,002,400
----------
30,238,787
==========

If you "cut the last three digits" (with TRUNC formula) you get:

10,234
10,001
10,002
------
30,237  <--- really, really bad result
======

If you "round" (with ROUND formula) you get:

10,235
10,001
10,002
------
30,238  <--- close, but has &quot;rounding error&quot;
======

If you just use the display format, you get (visually):

10,235
10,001
10,002
------
30,239  <--- appears wrong, but is really the most accurate.
======

See the difference?