How do you stop Excel from rounding?

[rccline]

I want to add a column of fractions, but i need excel to stop the rounding.

For example:

0.33333333
0.33333333
0.33333333
1.00000000

I need the displayed values added, i.e. 0.999999, not the fraction which is not displayed =(1/3)

Thanks,


Robert

 

[sfvb]

If the column width isn't wide enough to show all the decimal places, it will round off.

 

[SFTechie]

You can also format the cells to give you up to 30 decimal spaces. To do so, click on:
format > cells (ctrl+1) > number > decimal places.
If you set the category on General, the only way to see the decimal is to widen the column.
Hope this will help!

 

[sfvb]

Also, if the precision of the decimal places is more than the precision that is formatted, then rounding will take place.

For example,
values entered:
0.333
0.333
0.333
=sum(A1:A3)

values shown when cells formatted to 2 decimal places:
0.33
0.33
0.33
1.00

values shown when cells formatted to 3 decimal places:
0.333
0.333
0.333
0.999

To automatically overcome this you could check the "Precision as displayed" checkbox in the Options menu.
(Tools-->Option-->choose the Calculations tab), however I wouldn't recommend this.

 

[DaleWatson123321]

My 2 cents worth...

To be as precise as possible, I'd recommend using BOTH:
a) the ROUND function and b) Formatting
...to the same number of decimal places.

For example, if the individual numbers are derived via formulas, use =ROUND(xxx,8) - where xxx is your existing formula. Do the same with the "total" formula.

Then format all the cells as: Number - 8 decimals.

Hope this helps.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca

 

[rccline]

Fantasic... It works like a charm. Thank you all for your help.

Robert