how to keep cell rounding to 2 places even when someone else pastes number with more.

[flyers2thecup]

At work, i receive spreadsheets of bonuses that can be derived from
formula. however, when i copy and paste the info into our file fo
payroll, it can only be 2 places, otherwise it kicks out an error.

now, i've been here almost a year now and it's been near impossible t
get the people who submit the file to make sure they ROUND the numbe
to 2 places.

i'm wondering if there is something i can do in the cell that when the
paste their number in it, it automatically rounds.

or if that's not possible, if there's a way for me to highlight
column then have it round all the numbers in that column accordingly.

thanks in advance!!

 

[joeldsmt]

highlight the whole colum
right click and do a format cells
go to the number tab
choose number or currency and on the right side make sure it says tw
places. it should work

 

[flyers2thecup]

thanks for the response but, that doesn't do it.

that only gives the ILLUSION of 2 decimal places. the true numbe
still keeps the extra places. try it. you'll see that the numbe
changes in the cell, but remains the same in the formula bar.
even if you copy, paste special - values, the number still keeps th
extra places beyond the decimal.

anybody else have any other suggestions

 

[Mike]

When you paste the data into your spreadsheet use the "Paste Special"
and then select "Paste Link" from the dialog box. That worked on my
sample and I'm using Excel 2000.

Mike

 

[Guest]

There is an option that you can set to take care of this.

Tools>>Options>>Calculation
Check the box for Precision as Displayed. This will do it for you.

Bob Sullivan

 

[flyers2thecup]

thanks all!


BOB.....RIGHT ON THE BUTTON!!!! worked perfect!!!!!


I BOW TO THEE!!!!.....now get working on my other issues! ;

 

[AlfD]

Hi!

I think I would avoid the "precision as displayed" route, because i
affects the whole workbook indiscriminately.

Better, I think, to look to ROUND().

This way, you can actually remove the "spare" decimal digits, no
merely change their display characteristics.

Example:A1 holds 23.47653
Round(A1,2) returns 23.48 and that is all.

You could perhaps either "treat" your users' data to this operatio
before it gets to you, or clean it when it gets to you.

Al