Problem with large decimal places in Excel

user_14044881 0
Viewed 274

I'm currently trying to upload a multi-sheet excel document to a web-based application but running into an issue with floating-point numbers being added to the sum (see attached pictures). The total of the percentage values should add up to 100, however, they do not. The sum at 12 decimal places is 100.000000000000:

(12 decimal places)

but when the decimal place is extended to 13 it is 99.9999999999999:

(13 decimal places)

The web application I'm uploading to is reading it at 13 decimal places or higher from excel. I truly only need 6 decimal places but can't find a working solution. The round and absolute function have proven ineffective as well as the advanced option "set precision as displayed". Is it possible to adjust excel to sum at 6 decimal places or a specified amount?

I'm using Excel 2016. And the data is as follows. (92) rows of the value 1.075268. (1) row of the value 1.075344.

Any advice or recommendations would be greatly appreciated. Please let me know if I need to elaborate on anything.

excel-2016 floating-accuracy
1 Answers

Apparently this has nothing to do with the values you enter or how the cells containing them are formatted regarding decimal places, but with how Excel stores them internally and displays them:

enter image description here

[Don't get confused by the decimal commas usual here.]

If you look at <the>.xslx/xl/worksheets/sheet1.xml with a ZIP-program:

  ...
  <c r="A1" s="1">
    <v>1.0753440000000001</v> ... displayed as 1,075344‾0
  </c>
  <c r="A2" s="1">
    <v>1.0752679999999999</v> ... displayed as 1.075268‾0
  </c>
  ...
  <c r="C2" s="11">
    <f>SUM(A:A)</f>
    <v>99.999999999999844</v> ... displayed as 100.‾0
  </c>
  ...
  <c r="E1" s="9">
    <v>1</v>
  </c>
  <c r="E2" s="9">
    <v>1.1000000000000001</v> ... displayed as 1.1‾0
  </c>
  ...
  <c r="F11" s="14">
    <f>SUM(E:E)</f>
    <v>99.999999999999872</v> ... displayed as 99.999999999999‾0
  </c>
  ...
  <c r="K1" s="9">
    <v>91.2</v>
  </c>
  <c r="K2" s="9">
    <v>1.1000000000000001</v> ... displayed as 1.1‾0
  </c>
  ...

However, as Martheen mentioned in the comment to your question, you don't send the sum but the individual values to your application anyway.

user_17447740
Related