Trouble combining negative numbers - me or my formulas?

Churchy LaFemme

Board Regular
Joined
Sep 22, 2010
Messages
128
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.

Category
Expenses
Costs
Bob
Mort
Teddy
Others
Sandwiches
$50
Yes
Beer
$99
Yes
Staffing
$26
Yes
Noisemakers
$34
Fireworks
$103
Yes
Staffing
$52
Yes
Beer
$29
Yes
Hand soap
($56)
Yes
Noisemakers
$12
Yes
Fireworks
$14
Yes
Sandwiches
$75
Yes
Beer
$102
Yes
Hand soap
($123)
Yes
Noisemakers
$9
Yes
Fireworks
$45
Yes
Staffing
$103
Yes
Staffing
$88
Yes
Hand soap
($16)
Yes
Noisemakers
$22
Yes
Fireworks
$26
Yes
Sandwiches
$13
Yes
Beer
$204
Staffing
$29
Yes
Staffing
$203
Yes

<tbody>
</tbody>

Any thoughts? Quick tips for dealing with negative numbers?

Thank you!
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,974
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top