Sum not working

[ShaunAldridge]

Hi,

I have an issue whilst summing and it's really making my brain hurt. I hope someone can help me please!

I have a list of numbers that are all rounded to 2dp, which I have confirmed by selecting each cell and checking each value. The total of the values is 8579.52 which the sum function gives correctly in the cell that it's calculated in. BUT, when I paste values of the total it gives 8579.51999999999. I cant for the life of me figure out which value isn't rounding to 2dp correctly. The same thing happens if I use a sumif but it seems to work correctly if I sum half of the values, then the other half and add them together!


Does anyone have any ideas? I'm trying to import a csv to our accounting system and it is rejecting it because I guess it can see that one of the values isn't rounded correctly.

Thanks,

Shaun

 

[Joe4]

Welcome to the Board!

How exactly are you rounding?
Note that changing the Cell format to 2 decimal places does NOT chnages the actual values, it just changes the displayed value.
If you want to actually round the values, you need to use the ROUND function.

This issue is due to the Floating Point Arithmetic issue.
See here: Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?” | Microsoft 365 Blog

 

[Alex Blakenburg]

To check which value, is causing your issue just add a column and compare it to a rounded value.
So checking A2,
=A2=Round(A2,2)
It return a true or false.
Then filter on false.

Where are you pasting it to?
Is that an intermediary step to outputting it to csv ?
Typically an export to csv will keep the formatting, so you want it formatted as Number with 2 Decimals and ni commas.

 

[ShaunAldridge]

I have rounded all of the values to 2 dp using the ROUND function. I have skimmed through the Floating pint website and tried using the Precision as displayed option but that has not fixed the issue

 

[ShaunAldridge]

To check which value, is causing your issue just add a column and compare it to a rounded value.
So checking A2,
=A2=Round(A2,2)
It return a true or false.
Then filter on false.

Where are you pasting it to?
Is that an intermediary step to outputting it to csv ?
Typically an export to csv will keep the formatting, so you want it formatted as Number with 2 Decimals and ni commas.

I have done =A2=Round(A2,2) they are all fine. It's only when summing them that I get the extra dps. I am only pasting into the same csv whilst trying to find the problem value. There is no intermediary step.

 

[Alex Blakenburg]

You normally do a save as csv how are you pasting as csv ?
(csv means having commas so you can’t just copy in your columns)
What do you mean by “same” csv ?
If you are just doing a balancing check and have rounded the individual numbers, Round(Sum(),2)

 

[ShaunAldridge]

You normally do a save as csv how are you pasting as csv ?
(csv means having commas so you can’t just copy in your columns)
What do you mean by “same” csv ?
If you are just doing a balancing check and have rounded the individual numbers, Round(Sum(),2)

Sorry for not being clear. I have a csv file that I'm trying to import to our accounting system but it's being rejected, I'm assuming because of this summing issue that I've found. The fact that it's a csv is actually irrelevant because the problem is still there in a normal excel file.

So I have a list of values that I have rounded using the round function. When I sum them it gives the correct value of 8579.52. But when I copy this summed value and paste special values it gives 8579.51999999999. All of the original values seem to have rounded correctly and I have clicked into each and they all only have 2dp

 

[Joe4]

The order of operations and the manner in which you are doing this is VERY relevant (the devil is usually in the details, and different methods can provide different results).
So let's start from scratch:

1. Is this file in its original state an Excel file or a CSV file?
2. If it is a CSV file, how are you opening it in Excel (exact steps/process please, as there are multiple ways to do this)?
3. After you edit this Excel file to apply the rounding, how exactly are you doing this copy/pasting?
- Where are you pasting to?
- If you are pasting to another Excel cell, are you updating the format of the cell you are pasting to in order to only show two decimal places?
4. Exactly how then are you converting/saving this from Excel back to a CSV file?

 

[Alex Blakenburg]

Can you share the rejected csv file by sending us a link ?
If not can you copy in the first 5 lines and last 5 lines, as text (not excel) ?
How many lines in the file ?
Does the accounting system give any error message?
Is the accounting system SAP ?

 

[curiouscat408]

Sorry, I'm in a rush to get out....

I have done =A2=Round(A2,2) they are all fine

That is not good enough, due to an anomaly in Excel. Use A2-Round(A2,2)=0 or ISNUMBER(MATCH(ROUND(A2,2),A2,0)).

tried using the Precision as displayed option but that has not fixed the issue

Do not use Precision As Displayed. That causes more problems than it fixes, usually.

If you applied PAD to an Excel file, throw the file away and start over (!). PAD might have made some unintended changes that you have not noticed yet.

I'm trying to import a csv to our accounting system

Be sure that cells are not formatted as General. Use Number, Currency, Accounting or a Custom format that specifies 2 dp.

Normally, when Excel saves as CSV, data is written as they appear in cells. But if the cell format is General and the values have additional fractional digits, Excel writes them to the CSV file, even if they do not appear that way in the cell due to the column width.

Open the CSV file with Notepad, not Excel, in order to see exactly what is written to the CSV file.

total of the values is 8579.52 which the sum function gives correctly in the cell that it's calculated in. BUT, when I paste values of the total it gives 8579.51999999999

Upload an Excel file to a file-sharing website that demonstrates both behaviors: the original data and formula(s) that sum to 8579.52, and the pasted data and formula(s) that sum to 8579.51999999999. Upload two separate Excel files, if necessary. Post the shared download URL of the file(s).

This forums XL2BB feature is not good enough for this kind of problem, which depends on the exact internal binary values. I don't know about the new "upload mini-sheet" feature.