Formula not equating to zero.

[dhosi439]

A bit of an explanation regarding my workbook. I have two worksheets. The Data sheet containing the table 'Table_Data' which contains ledger transaction amounts and LedgerSummary which contains the table 'Table_Summary' detailing account transaction totals.

I use SUMIFS to determine values for each account in my 'Table_Data'

sum_range = Table_Data amounts
criteria_range1 = Table_Data account names
criteria1 = Table_Summary account names
criteria_range2 = Table_Data cleared, which contains a date or a text value
criteria2 = >=0 to ensure if the field is a date by checking if it is greater than zero

The sum is performed for each individual account based on matching the account name in Table_Summary to Table_Data and if a numerical value or date is present. If a text value is present it doesn't include that record in the sum.

=SUMIFS(Table_Data[Amount],Table_Data[Account],"="&Table_Summary[Account],Table_Data[Cleared],">=0")

This works fine for several accounts that equate to zero. However for this one account the value is not equating to zero, even though the values for that account do indeed equal zero.

These are the values for this account.

[TABLE="width: 716"]
<tbody>[TR]
[TD]-247[/TD]
[/TR]
[TR]
[TD]-61.1[/TD]
[/TR]
[TR]
[TD]-10.27[/TD]
[/TR]
[TR]
[TD]10.27[/TD]
[/TR]
[TR]
[TD]61.1[/TD]
[/TR]
[TR]
[TD]247

When I filter the table to only show this account, the status bar shows a sum of,

[TABLE="width: 149"]
<tbody>[TR]
[TD="align: right"]-2.84217E-14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Which is also the value the formula equates to. I am using conditional formating to show values less then zero in red, values greater than zero in green, and zero as black text. All values are formatted to accounting. This is the same scenario for both tables. I have copied the values from the Table_Data to a blank sheet and come up with the same sum issue, but if I manually type the values into a blank sheet the issue is not present.

I have also tried copying the informational data and manually typing the values into Table_Data with no success.

I am at a loss as to why only this set of values is causing an issue. I have increased the decimal place up to thirty times and do not see any hidden values in Table_Data.

 

[konew1]

Where does your data come from?
If it is downloaded from an accounting system, or calculated in excel, then the values may have decimal places not showing when you have the data formatted as accounting. Change the formatting to show more decimal places then find the specific items that have additional decimal places, then decide of the best way to resolve. =Round( is the obvious method, but that can have implications if you use the data elsewhere.

 

[xenou]

Or, besides hidden decimal values in your original data, you may also just be experiencing a normal effect of using computers with decimal values. Googling Excel floating point arithmetic will get you many examples. The number you see is an extremely tiny error (virtually zero). Simplest is to format the cell with number format and it will appear as zero as you expect.

P.S.,
This blog post is a good one (as Joel's often are):
http://www.joelonsoftware.com/items/2007/09/26b.html

 

[dhosi439]

I am manually entering the data. I thought about using round, but I would prefer to remove the issue then hide it. I guess at this point it is just an issue related to using decimals.

Thanks for the information xenou.