MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Round function question


Posted by Hansoh on February 15, 2002 12:08 PM

i send templates out to different groups and they return them to me with formulas filled out in these templates.

i need to upload these values into a financial reporting system. however, the outside system does not accept values with decimal #s.

i ask the people who submit these templates to either use the ROUND function in their formulas or submit values (NOT formulas).

basically, i need to apply the ROUND function to all these formulas. it's kind of a pain in the ass to do FIND/REPLACE 'round()' OR cut paste special values then round OR (God forbid) manually round.

what is the MOST efficient and MOST elegant method to apply the ROUND function to a motley worksheet? is there some trick that you know of? please advise.


-han


Posted by Mark W. on February 15, 2002 12:15 PM

Format your cells as 0, choose the Tools | Options...
menu command, click on the Calculation tab, and
check "Precision as displayed".

Posted by Hansoh on February 15, 2002 12:19 PM

YES!

man, you're fast.

while you were typing your response, i went to the site's archives and came upon another post re: the CALC>PRECISION AS DISPLAYED option. when i played around with it, i got a warning that says something like...

data may permanently lose accuracy...

could someone please offer up some insight into this? maybe some warnings based on your own experience? thanks!


han

Format your cells as 0, choose the Tools | Options...

Posted by Mark W. on February 15, 2002 12:25 PM

Re: YES!

This technique will permanently change the
precision of values enter into a cell (e.g.,
3.1415 will become 3). =PI() will also produce
3; however, it will revert to full precision
once you uncheck this option.

It shoulds like this approach is a good fit
for your requirements. If you want to preserve
the original worksheets work with a copy (or
don't save the changes caused by this option).
Also, use Save As... to create a new uploadable
file.

man, you're fast. while you were typing your response, i went to the site's archives and came upon another post re: the CALC>PRECISION AS DISPLAYED option. when i played around with it, i got a warning that says something like... data may permanently lose accuracy... could someone please offer up some insight into this? maybe some warnings based on your own experience? thanks!

Posted by Mark W. on February 15, 2002 12:29 PM

Repost: Re: YES!

This technique will permanently change the
precision of values enter into a cell (e.g.,
3.1415 will become 3). =PI() will also produce
3; however, it will revert to full precision
once you uncheck this option.

It sounds like this approach is a good fit
for your requirements. If you want to preserve
the original worksheets work with a copy (or
don't save the changes caused by this option).
Also, use Save As... to create a new uploadable
file.