spreadsheet totals show diff bal than pivot table

[goldenhearttx]

I'm working with files that have a fairly large number of lines (either 994K or 150k after filtering). These files show ytd entered amount that is off by two cents- should net to zero. When split to dr/cr (pos/neg) and remove the "zero" bals, they become off by six cents. They can be in balance with one sort, but not with another.
I have looked for decimal places more than two digits. The individual lines do not have anything more than two digits to the right of the decimal. The total, however, has 16 or 18 digits to the right of the decimal.
To try to find the differences I did a pivot table. The pivot table shows the journals would be in balance when split to how the system would balance the journals, but the grand total is still off by the six cents.

I have no idea how these issues can happen, and if you had told me yesterday that it could I would have said you were crazy

I need to get journals loaded from this information, but when the totals are off it won't load (that's a good thing right?). Any ideas how to find/fix the problem lines?

 

[Johnny Thunder]

A couple of ideas here,

1. Your 0.00 may actually have values further down in the decimal places that maybe you missed?

2. Most systems don't look at the decimals past the 2nd so why not ad a formula to scrub off the decimals past two so you get a cleaner rounded number. (ex. 6.05 would look the same to a system as 6.047547 so why not do a formula to correct) This may help to isolate the issue.

Just some suggestions.

 

[goldenhearttx]

Thanks for the response JT
The raw data file shows zeros in the fields
I did a "round" formula to two decimals on the data and then pasted values in the original cells, and it still does this. Someone at work found an old article that talked about the way it's stored in binary that may be causing the issue.
I would have thought the pivot table would have highlighted the differences but it makes it look like it's in balance all lines on the pivot have dr = cr, but the grand total dr <> cr. Very strange to me

 

[Johnny Thunder]

If it wasn't so many rows of data I would offer to look at the data and see if I could come up with anything.

I do suggest you upload the data to a cloud storage like google drive or dropbox and link it here. Someone else may see your question and offer up some more help.

Another though I had was, sometimes when I have issues like this I will paste and Value columns (columns with totals) into Notepad because it will remove any weird formatting that some Systems like SAP/Oracle put into the numbers, then paste special values into a completely new sheet and re-do any pivots to see if this solves the issue.

 

[fogster74]

It's 2022, I've been a Power User of Excel for 25 years... and I've just run into this problem today. No matter what I do, I cannot reconcile a very simple table total with a Pivot Table summary that shows a total of 0.000000000000133582.

I have even manually rekeyed every single value myself - it's still the same.