Copied Values Rounded to 1,000s

[User0091]

I need to copy over a long column of numbers from one application
where the numbers are in dollars to an Excel spreadsheet where I need
them displayed rounded to Thousands.

I copy the numbers over using copy-paste special-values, which
displays the numbers in dollars with many decimal points (for example
121,454.328514).

Is there a quick and easy way to select the whole column of numbers
and round them all to the thousands (121.5) instead of rounding them
to the thousands one by one by putting /1000 at the end of each
number? Thanks in advance.

 

[Bernd P]

Hello,

I suggest a helper column:
B1:
=ROUND(A1,-3)
and copy down.

Finally you can copy column B and paste special values only to A or
elsewhere.

If you like to speed this up a little bit, you can copy column A to B,
select all relevant cells in B (select B1 and press CTRL+SHIFT
+DOWNARROW), remember last used row number and array-enter
=ROUND(A1:A37542,-1)
(enter with CTRL + SHIFT + ENTER, not only with ENTER), if 37542 was
last used row. Finally you can copy values...

Regards,
Bernd

 

[Sox]

I need to copy over a long column of numbers from one application
where the numbers are in dollars to an Excel spreadsheet where I need
them displayed rounded to Thousands.

I copy the numbers over using copy-paste special-values, which
displays the numbers in dollars with many decimal points (for example
121,454.328514).

Is there a quick and easy way to select the whole column of numbers
and round them all to the thousands (121.5) instead of rounding them
to the thousands one by one by putting /1000 at the end of each
number? Thanks in advance.

Your opening paragraph describes a slightly different problem than the
closing paragraph -- the opening paragraph says you only need to display
them in thousands of dollars while the last paragraph implies you realy want
to round the values.

If you want to ROUND the numbers (i.e., if it's okay to lose some precision
in the original values), a previous poster provided that answer.

If you only need them DISPLAYED as thousands of dollars while keeping the
original values, then select the column and format the entire column as
either:

#,##0.0,

or

$#,##0.0,

depending on whether you want a dollar sign in front. NOTE THE EXTRA COMMA
AT THE END OF THE FORMAT

 

[joeu2004]

Is there a quick and easy way to select the whole column
of numbers and round them all to the thousands (121.5)
instead of rounding them to the thousands one by one by
putting /1000 at the end of each number?

The varied answers in this thread reminds me of the parabole about the
elephant and the blind men. Each person "sees" something very
different depending on the part of the body they touch.

One person focused on your phrasing "rounding them to the thousands"
and offered a solution with ROUND. Of course, that is not what you
want at all, since 121.5 is not "rounded to the thousands" in any
sense of the word. (122 might be. But the ROUND suggestion does not
give that result.)

Another person saw correctly that you want to __display__ numbers in
"units of a thousand" and offered a formatting solution that seems to
match your example.

I see that you said that dividing by 1000 is your desired solution;
it's just that you don't want to do it "one by one". If you want the
__value__ of cell, not just its appearance, to be in units of a
thousand, you might consider the following.

1. Cut-and-paste-special the column of numbers as you are doing now.

2. Put 1000 into a cell; then select the cell and copy it.

3. Select the column of numbers, and do paste-special-divide.

4. With the column of modified numbers still selected, format them
with the desired number format and decimal places.

Note: Step #4 will cause Excel to round the __displayed__ number, but
not the actual value. If you want the actual value rounded to to some
fractional unit of a thousands (e.g. 1/10 thousands in your example),
use the ROUND suggestion after step #4 with some modification. For
example, =ROUND(A1,1) will round to one decimal place.


----- original posting -----