Error with Excel count function

Hi!

I came across something really wiered with the count function, and would like to see if anybody could help me to figure it out?

I selected all the cells in one column from row 3 to row 238, and the count number showing at the bottom of the screen (on status bar) was 235 - it should be 236 instead! Of course the sum was also wrong, because it was missing one cell. I went back and reviewed line by line and there is no blank cell (cells with zero value has zero in them). I also used pivot table to calculate the sum and the total numbers of cells - it came out right!

That's just wiered.... I wonder if anybody had similar experience, and what's the fix?

By the way, I was working on the trial balance of all the general ledger accounts exported out from Quickbooks. The total value of all accounts should sum to zero (as that's how the accounting double entry system works), but in this case it didn't - that's when I found out the count function missed one cell, but couldn't figure out which one.

Jenny

A common explanation is that one cell contains text that only appears to be a number. Confirm by entering =ISTEXT(A3) in a parallel column and copy down through row 238, assuming that column A contains the original data.

If that does not explain the problem, I suggest that you attach an example Excel file (redacted) that demonstrates the problem.

Hi,

try to add a 0 (zero) using paste-special add values to the numbers. Just to see if you detect any change.

Another way :Select your column - Data ribbon - Click " Text to column" then immediately " Finish"

Here to learn said:

By the way, I was working on the trial balance of all the general ledger accounts exported out from Quickbooks. The total value of all accounts should sum to zero (as that's how the accounting double entry system works), but in this case it didn't

Click to expand...

Your original posting identifies one potential problem: a cell that is not counted as numeric.

But beware that the sum might not be exactly zero, even if all cells are included in the sum correctly.

It is prudent to explicitly round arithmetic that you expect to be accurate to a specific number of decimal places. For example, =ROUND(SUM(A3:A238),2)).

The issue is related to how Excel stores numbers, namely 64-bit binary floating-point. Most decimal fractions cannot be represented exactly. Consequently, arithmetic often results in infinitesimal differences, which you may or may not see because Excel formats only up to 15 significant digits, rounding the 16th.

To demonstrate, note that =10.12-10-0.12 formatted as General displays an infinitesimal difference: about -7.77E-16, which is read as -7.77 times 10 to the power of -16. But =ROUND(10.12-10-0.12,2) returns exactly zero. We can see that if we format as Scientific: 0.00E+00 is exactly zero.