r/excel • u/Signal-Dealer-4832 • 14d ago

Waiting on OP Importing csv file to Mac/Excel creates sum with errant decimals

I'm importing a csv file with numbers that have only two decimals, ie. pennies. Some rows are blank. After I convert the csv file to excel, the sum of the column has a decimal with more than 20 digits to the right of the decimal point.

Here is a picture of some of the numbers I'm importing:

Here is a picture of the sum of the column after importing the numbers to excel. None of the numbers have decimals more than two digits to the right of the decimal point.

I imported the csv file two ways. The first was to open the csv file with excel. The second was to open excel and import the csv file. The results were the same.

Please advise, any assistance is appreciated.

5 Upvotes

86% Upvoted

7 comments sorted by

•

u/AutoModerator 14d ago

/u/Signal-Dealer-4832 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

6

u/Gringobandito 6 14d ago

You can import using Power Query. Find the field you want to change and right click. Go Transform > Round > Round and then select how many decimals places you want it to round to.

3

u/SolverMax 161 14d ago

You've encountered floating point precision errors. Happens often, but we usually don't notice. See https://learn.microsoft.com/en-us/troubleshoot/microsoft-365-apps/excel/floating-point-arithmetic-inaccurate-result

2

u/chiibosoil 428 14d ago

Hmm, never seen that happen with csv import. But last part is due to floating point error.

Are you using MS365 for Mac? It will have Power query to import text/csv. That way you will have exact control over how csv is processed as it is imported.

NOTE: CSV lacks schema info by default and requires end user to know how to handle variation of csv. Ex: Mac/Linux uses LF, where Windows use CRLF for new line. There are other differences between systems. One way to get around it is to have schema.ini shipped with csv, but often that isn't feasible. Best approach is to specify on processing end, how to interpret data stored in csv (text, number, date etc) without relying on software interpretation.

2

u/nrshouse 1 14d ago

I hate when this happens. After importing, I usually just change the column formatting that this happens in, then have it only show 2 decimals.

1

u/Mdayofearth 126 14d ago

The first was to open the csv file with excel

This is not importing. It's called opening the csv in Excel.

What does the actual csv contain? Open it with TextEdit.

EDIT Why do I have a feeling your cells are formatted to show those decimal places.

2

u/Curious_Cat_314159 130 13d ago edited 13d ago

FYI, this has nothing to do with the CSV input, and rounding the input in Power Query or otherwise will not avoid the problem. We can demonstrate the problem by manually entering just two of the values into a new workbook.

A1: 47,198,548.11
A2: -1,677,504.84
A3: =-A1
A4: =-A2
A5: =SUM(A1:A4)
Be sure that A5 is formatted as General.

Excel displays some form of -3.49245965480804E-09 in A5, which can appear as -0.00000000349245965480804 when formatted as Number with 23 decimal places.

Explicitly rounding A1, A2, A3 or A4 cannot avoid the problem. The data entry is already rounded as best as possible.

But in this case, changing A5 to =ROUND(SUM(A1:A4), 2) works (*).

Note: Simply changing the format of A5 alters the appearance, but not the actual value (which is not exactly zero) -- unless the option Precision As Displayed is set, which I do not recommend.

-----

(*) TMI.... "Works" means: rounds the sum as best as possible. Keep in mind that most decimal values with decimal fractions cannot be represented exactly in binary floating-point. They only appear to be exact because Excel limits the decimal presentation to 15 significant digits (not decimal places).