spreadsheet totals show diff bal than pivot table

goldenhearttx

New Member
Joined
May 23, 2018
Messages
3
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 :confused:

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

Well-known Member
Joined
Apr 9, 2010
Messages
578
Office Version
2016
Platform
MacOS
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

New Member
Joined
May 23, 2018
Messages
3
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

Well-known Member
Joined
Apr 9, 2010
Messages
578
Office Version
2016
Platform
MacOS
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.
 

Forum statistics

Threads
1,082,323
Messages
5,364,586
Members
400,809
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top