zero balance

Hi Kevin
Very weird. I have no idea why it's doing it, and you've probably thought of this already, but if you change your formula in E11 to =IF(G11=C11,"no diff",G11-C11) it appears to fix the problem and still gives you the info you need. I'll be keeping a close eye on this post to see why it's doing this though!
Cheers
Ian

I see a very small number in E11, which is practically 0. The copy that I have shows a "General" cell format. I'd change to Number, say 2 decimals. Ian's suggested change to your IF formula in this cell a good one. I felt also compelled to rewrite it as follows:

=IF(G11-C11,G11-C11,"no diff")

Cheers.

Aladin

Better...

Kevin,

The change to the IF-formula that I suggested doesn't make much sense.
I believe what follows is better:

=IF(ISNUMBER(G11),IF(ISNUMBER(C11),G11-C11,""),"")

Provided that you format E11 as Number.

Aladin

Hi Kevin

I just took a quick look and nothing jumps out to point to any reason why this is happening. Try reproducing the problem on a New sheet. It is not unusual for a Worksheet to corrupt for no apparent reason.

Dave
OzGrid Business Applications

Thanks to all of you. See notes.

Yes, this is a real brain cramp.

Dave: Thanks for idea of copying to another file, I'll try it.

Ian: I like the formula change. Heck, anything is worth a try.

Aladin: Very creative. I was going to try this in both C11 and G11: =INT([CurrentFormula]*100)*0.01 That would for sure compare apples to apples.

All: Have a wonderful life!

Kevin

Re: Thanks to all of you. See notes.

Kevin
Here is some useful info that may enlighten you.
Floating-point mathematics is a complex topic.
Microsoft Excel was designed around the IEEE 754 specification
with respect to storing and calculating floating-point numbers.
IEEE is the Institute of Electrical and Electronics Engineers,
an international body that, among other things, determines standards
for computer software and hardware.
When storing numbers, a corresponding binary number can represent every number or
fractional number. For example, the fraction 1/10 can be represented in a
decimal number system as 0.1. However, the same number in binary format becomes
the repeating binary decimal

0001100110011100110011 (and so on)
Because there is no fractional part to an integer, its machine representation is much simpler than it is for floating-point values. Normal integers on personal computers (PCs) are 2 bytes (16 bits) long with the most significant bit indicating the sign. Long integers are 4 bytes long. Positive values are straightforward binary numbers. For example:

1 Decimal = 1 Binary
2 Decimal = 10 Binary
22 Decimal = 10110 Binary, etc.

However, there are some limitations of the IEEE 754 specification which fall into
three general categories:

- maximum/minimum limitations

- precision

- repeating binary numbers

Excel offers two basic methods to compensate for rounding errors: the ROUND
function and the "Precision as displayed" workbook option.

Method 1: The ROUND Function:

The following example using the data above, uses the ROUND function to force a
number to five digits. This allows you to successfully compare the result to
another value.

A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5)

results in 1.00012

D1: =IF(C1=1.00012, TRUE, FALSE)

results in the value TRUE

Method 2: Precision as Displayed:

In some cases, you may be able to prevent rounding errors from affecting your
work by using the "Precision as displayed" option. This option forces the value
of each number in the worksheet to be the displayed value. To turn on this
option, click Options on the Tools menu. On the Calculation tab, click to select
the "Precision as displayed" check box.

For example, if you choose a number format showing two decimal places and then
turn on the "Precision as displayed" option, all accuracy beyond two decimals
will be lost when you save your workbook. This option affects the active
workbook including all worksheets. You cannot undo this option and recover the
lost data. It is recommended that you save your workbook prior to enabling this
option.

I'd personally use the Round function

Ivan