Rounding won't round!

[AgentMeow99]

Hi all,

I have numbers in column B, in column C I have =round(b2,2). Then I copied column C and pasted special values into column B. When I add decimals to column B and look at the total number in the lower right hand bottom of the screen it shows my total as 1,333,844.710005000

The system I have to upload my file to won't accept the upload due to this - it has to be just 2 decimals.

Can anyone give me a suggestion as to how to get is of this 5?

Thank you!!

 

[Trevor G]

You could try the TRUNC formula as well: =TRUNC(B2,2)

I have tried both and they work, so could you show some example data before, once you then add the formula, and then after the Paste Special. It might be an idea not to highlight the whole Column just the data in it.

 

[AgentMeow99]

That didn't work the 5 is still there.

 

[shg]

You need to round the sum.

 

[AgentMeow99]

There isn't a grand total at the end of the data. If I highlight the entire column, the sum at the lower right on the screen shows the total of the column as 1,333,844.710005000

 

[Trevor G]

Then what is in each cell after you have done the calculation and copy paste special, if only 2 decimals are being shown then the system you want to upload to should accept it.

 

[AgentMeow99]

That's the issue. None of the cell have anything past the the last 2 digits, but when I highlight the entire column it shows that 5... The system will not accept the upload.

 

[shg]

Your computer, and therefore Excel, uses double-precision binary floating-point arithmetic. It cannot store most decimal exactly, in the same way that a base-10 decimal with finite length can't contain 1/3 exactly.

There is surely a workaround, but we don't know (I don't know) enough about what you're trying to do, and what the limitations are, to make a suggestion.

 

[joeu2004]

I have numbers in column B, in column C I have =round(b2,2). Then I copied column C and pasted special values into column B.

If the numbers in column B are constants with only 2 decimal places that you enter manually, there is no need to round them (again) in column C.

When I add decimals to column B and look at the total number in the lower right hand bottom of the screen it shows my total as 1,333,844.710005000

In general, whenever you expect a calculation with decimal fractions to be accurate to n decimal placed (n=2 here), you should explicitly round to that number of decimal places. Do not round to arbitrary number of decimal places (e.g. 10), as some people suggest. That does not always work well enough.

So if your total is SUM(C2:C1000), change it to ROUND(SUM(C2:C1000),2).

You might think that is superfluous because your data in column C is already rounded to 2 decimal places.

But the infinitesimal arithmetic differences arise because Excel uses 64-bit binary floating-point to represent numbers internally, and most decimal fractions cannot be represented exactly in that binary form. Moreover, the approximation of a particular decimal fraction depends on the magnitude of the integer part of the number.

For example, the exact decimal representation of the internal binary value is shown on the right:

 123.34  123.340000000000,003410605131648480892181396484375
 567.34  567.340000000000,0318323145620524883270263671875
1234.34 1234.33999999999,9918145476840436458587646484375

I use period for the decimal point and comma to demarcate the first 15 significant digits, which is all that Excel chooses to format (rounded) arbitrarily, replacing any digits to the right with zero.