How not to round a formula

[Janet]

I am using SUM to add up a column with numbers that have six decimals. I do
not want the answer to be rounded up. How can I create a formula not to round
the answer, using SUM? Thanks

 

[Toppers]

Just SUM which will not round. If your numbers are exactly 6 decimals, then
display answer with 6 decimals.

You could format your cells with 7 decimals (last digit should be 0?) and
check SUM this way.

 

[Kevin B]

Excel does not automatically round an answer returned by a function, however,
the number of decimals used by the number format will cause a number to
display a rounded an answer. Set your numeric format to number with at least
6 decimal positions.

Select the range to format and click FORMAT/CELLS on the menu, click the
NUMBER tab in the formatting dialog box and select NUMBER from the CATEGORY
panel and set the DECIMAL PLACES spinner box to a minimum of 6 decimals

 

[bj]

are you sure the data you are adding has just six decimal significant figures
they may show 6 but actually have more in which case the sum would not
necessarily tshow the the total for what is shown.
change the nuber of significant figures in your cells you are adding to
verify.
you may need to round the input cells before adding.

 

[Dave Peterson]

And if your cell with the =sum() formula is formatted as general, maybe you're
only seeing 2 decimal places.

Try widening that column or change to a smaller font.

 

[Janet]

Wow! Didn't expect such great responses so quickly. In the case of the
formatting, the formula is formatted as a number with 6 decimal places (it
has to be). What if part of the formula has a hard number (like 100) and not
a cell reference? Would that be apart of the problem?

 

[Toppers]

What is the formula you refer to? Having a constant i.e. 100 should make no
difference.

It's difficult without having your data available to be more precise in our
replies.

As already pointed out, what you see as formatted doesn't always represent
the internal Excel value

12.3456789 would be seen as 12.345679 to 6 decimal places

 

[bj]

can you give an example of what you are doing and show the problem?
if the input data is 6 digits and the finial equatin is six digits it should
not have to round up. the the sum of what you see is not the same as what
sum() gives, then there is data past the sixth decimal place in your
incoming data, even if it shows only 6

 

[Janet]

The formula in question is:
=D30-(E11+E23)*D30/100
I checked the D column and E column and both are set with the formatting as
Number.

 

[Dave Peterson]

And what values are in
D30
E11
E23

And if you select the cell with the formula, then hit F2, then F9 and enter,
what do you see?

(hit edit|Undo to get the formula back)