1

I have an Excel workbook with raw numeric data that probably originated as FLOAT data in a database. Looking at the xl\worksheets\sheet1.xml doc inside the XLSX shows values like this (abbreviated to just the column of interest);

<c r="E2" s="3"><v>428315</v></c>
<c r="E3" s="3"><v>0</v></c>
<c r="E4" s="3"><v>3167839.84</v></c>
<c r="E5" s="3"><v>3050037.97</v></c>
<c r="E6" s="3"><v>2926514.28</v></c>
<c r="E7" s="3"><v>2800695.64</v></c>
<c r="E8" s="3"><v>2668935.5699999998</v></c>
<c r="E9" s="3"><v>2534727.5299999998</v></c>
<c r="E10" s="3"><v>2394350.12</v></c>
<c r="E11" s="3"><v>2251364.64</v></c>
<c r="E12" s="3"><v>2101973.2000000002</v></c>
<c r="E13" s="3"><v>1949806.21</v></c>
<c r="E14" s="3"><v>1790987.69</v></c>
<c r="E15" s="3"><v>1629218.48</v></c>
<c r="E16" s="3"><v>1460542.84</v></c>
<c r="E17" s="3"><v>1288733.3799999999</v></c>
<c r="E18" s="3"><v>1109752.94</v></c>
<c r="E19" s="3"><v>0</v></c>
<c r="E20" s="3"><v>182305.72</v></c>

Some of these values are clearly imprecise, i.e. 2668935.5699999998 instead of 2668935.57 in cell E8. When I look at the worksheet in Excel how do I make Excel display the raw data, i.e. the full imprecise value and not the rounded value? I've tried all of the standard format categories, none of which do what I want.

The reason for wanting to display raw data is that when Excel sums the values it uses the raw data, not the displayed figure, so differences arise. I'm aware of the Precision as Displayed setting, but this isn't what I want - the SUM is correct, I just want the screen to reflect what is being summed.

Edit I've created a sample file to demonstrate the issue. This file includes the raw data above and a few more rows for good measure. If you can't see the raw data (i.e. the value 2668935.5699999998 in cell A8) then look at sheet1.xml inside the xlsx to confirm it really is there. If you don't know about the XLSX file format then start here.

  • I would format the cells in the column as Number with 10 decimal places. It sounds like you may have tried that, so please explain how that doesn't "do what you want". – Bandersnatch Jul 25 '17 at 15:08
  • @Bandersnatch Using Number with any number of decimal places does not display the raw data, it displays a rounded value. For example, cell E8 displays 2668935.57 instead of 2668935.5699999998. – Rhys Jones Jul 25 '17 at 15:12
  • That can't be. It should at least display 2668935.5700000000. Could you send me a spreadsheet with a partial column of that data? steve_k@mindspring.com – Bandersnatch Jul 25 '17 at 15:18
  • That is because Excel only stores 15 digits and then rounds whats left. If you want to "see" the numbers you will need to store it as text, but that opens up other problems. – Scott Craner Jul 25 '17 at 15:22
  • @Scott, that's correct, but he should still see e.g. 2668935.5700000. Maybe that's what he meant. Rhys? And here is an explanation of Excel's precision – Bandersnatch Jul 25 '17 at 15:36
1

Try inserting a apostrephe (') in the beginning of the cell, this will show you the raw data within the cell.

You could also round each term before summing it using the Round function.

  • Editing the cell does not expose the raw data, it only exposes the rounded values, i.e. 2668935.57 instead of 2668935.5699999998. – Rhys Jones Jul 25 '17 at 18:25
  • Mine also shows only ...35.57, but when I insert the apostrophe, it shows the full number. – wizlog Jul 25 '17 at 18:26
  • (edited my comment) – wizlog Jul 25 '17 at 18:29
  • I tried, but I need a username and password to access it? – wizlog Jul 25 '17 at 20:00
  • I was able to open the file, and I tried adding the apostrophe. I think that forces Excel to interpret the cell as text. In any case, doing that did not show any hidden decimals, it just dropped the trailing zeros that were showing because I had formatted the column as Number with 10 decimal places. Here is what the apostrophe gave: '2668935.57 – Bandersnatch Jul 26 '17 at 2:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.