Rounding Decimals

J

Jim May

I've got 2 or 3 thousand figures
that display as (sample) 22.3% with actually
22.2849565849694 in the cell.
I need to "do what" ? in order to convert
them to display 22.28% with actually
..2228 in the cell?

Tks in advance
 
A

Anne Troy

If you want the actual value to be .2228 and the cell is actually
22.2849565... (and not a formula that results in that value), then you'll
have to create another column and do something like this: =round(a1/100,4).
Then convert those formulas to values and delete your original column.
************
Anne Troy
www.OfficeArticles.com
 
B

Bob Phillips

Format as '0.00%' without the quotes.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Duke Carey

Since you want .2228 in the cell, you are looking to round to the 4th decimal
place. You'd use

=ROUND(.222849565849694,4)

or

=ROUND(A1,4)

You may also want to look at the Help file for info on the TRUNC() ,
ROUNDUP() and ROUNDDOWN() functions
 
R

Roger Govier

Hi Jim

One way
=ROUND(A1*100,4)/10000
Format cell as number with 4 decimal places, or Percentage with 2 decimal
places.

Regards

Roger Govier
 
D

Duke Carey

Forgot to mention: put the ROUND() formula into an empty column adjacent to
your numbers and copy it all the to the end. Now select all the cells with
the new formula, copy them, select the original numbers and use
Edit->Paste Special-> Values to overwrite them with what you want.

BE SURE TO WORK ON A BACKUP COPY OF YOUR DATA
 
J

Jim May

=ROUND(A1,4) works perfectly;
Thanks,
Jim

Duke Carey said:
Since you want .2228 in the cell, you are looking to round to the 4th decimal
place. You'd use

=ROUND(.222849565849694,4)

or

=ROUND(A1,4)

You may also want to look at the Help file for info on the TRUNC() ,
ROUNDUP() and ROUNDDOWN() functions
Click to expand...
 
S

Sloth

Tools->Options->Calculation->Precision as Displayed

will round all numbers to the format you display them. Be carefull you will
permanently lose any accuracy. Also, after you finish you might want to turn
it off. It does funny things with formulas. For instance
A1=1+0.123
A2=A1

A2 will equal 1.12000000000000 if A1 is formatted to two decimal places and
you have "Precision as Displayed" checked.

"Precision as Displayed" is perfect for rounding massive amounts of imported
data, but I have not found any other viable use for it. Hope this helps.
 
Top