Trouble combining negative numbers - me or my formulas?

[Churchy LaFemme]

I am either doing something wrong or Excel is not handling negative numbers the way I expect it to.

I have data tab (similar to the dummy table at the end of this post) which has several million dollars posted on several thousand rows.

I have summary that shows the costs incurred for Bob, Mort, and Teddy, but that summary does not include the data for "Others."

On another tab, my checking tab, I sum to totals for the data to make sure that all of it has been allocated correctly.

Checking tab

First column​

Expenses​

Costs​

Second Column​

Bob + Mort + Teddy Expenses
Bob + Mort + Teddy Costs
"Others" expenses (They have no costs)​

The totals for first and second column tie to each other and to the totals rows on the data tab.

Third colum​

Bob + Mort + Teddy Expenses – "Soap" line items
Bob + Mort + Teddy Costs – "Soap" line items
"Others" expenses – "Soap" line items
Soap line items​

This does not tie. The expenses are off by one percent – which I might attribute to rounding errors, except that the other two columns and the totals rows on the data sheet tie to the penny. It is only when the category with multiple negative numbers is set off as a row that my totals don't match.

Are the negative numbers for some of the soap rows changing the totals? Or should I take a closer look at my method of summing the types of data?

For what it's worth, if I filter the soap lines on the data tab, that total matches the total from sumifs on my checking tab.

I do not have any line items that are allocated to more than one category. All the amounts are in the same format. There are not extra spaces anywhere.

[TABLE="width: 548"]
<tbody>[TR]
[TD]Category
[/TD]
[TD]Expenses
[/TD]
[TD]Costs
[/TD]
[TD]Bob
[/TD]
[TD]Mort
[/TD]
[TD]Teddy
[/TD]
[TD]Others
[/TD]
[/TR]
[TR]
[TD]Sandwiches
[/TD]
[TD]$50
[/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Beer
[/TD]
[TD]$99
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Staffing
[/TD]
[TD][/TD]
[TD]$26
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Noisemakers
[/TD]
[TD]$34
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fireworks
[/TD]
[TD]$103
[/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Staffing
[/TD]
[TD]$52
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Beer
[/TD]
[TD]$29
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hand soap
[/TD]
[TD]($56)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Noisemakers
[/TD]
[TD]$12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Fireworks
[/TD]
[TD]$14
[/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sandwiches
[/TD]
[TD]$75
[/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Beer
[/TD]
[TD]$102
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hand soap
[/TD]
[TD]($123)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Noisemakers
[/TD]
[TD]$9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fireworks
[/TD]
[TD]$45
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Staffing
[/TD]
[TD][/TD]
[TD]$103
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Staffing
[/TD]
[TD][/TD]
[TD]$88
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hand soap
[/TD]
[TD]($16)
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Noisemakers
[/TD]
[TD]$22
[/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fireworks
[/TD]
[TD]$26
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Sandwiches
[/TD]
[TD]$13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Beer
[/TD]
[TD]$204
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Staffing
[/TD]
[TD][/TD]
[TD]$29
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Staffing
[/TD]
[TD][/TD]
[TD]$203
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any thoughts? Quick tips for dealing with negative numbers?

Thank you!

 

[JLGWhiz]

I have found Excel to be very accurate in its handling of numerical data, given the exceptions related to percentages and how they are handled. Still, by the standards that Excel uses, even the percentages are correct. Excel uses the same rules for handling negative numbers that I was taught in school for adding, multiplying, dividing and subtracting them. So, I doubt that the problem is in Excel.