Formula anomaly

1 A B C D E F
2 What is wrong with the following?
3 The totals for these two sets of figures are the same - or are they
4 If you subtract one from the other using the simplest of formulas the answer is precisely zero
5 But if you introduce brackets into the formula the answer is not quite zero. How is that possible?
6
7 enter: 0.7 0.7
8 enter: 0.7 0.7
9 enter: 0.7 0.7
10 enter: 0.0 0.7
11 enter: 1.4 0.7
12 Totals 3.5 3.5
Difference between 2 totals: 0.000000000000000000000 using formula: =B12-C12
-0.000000000000000444089 using formula: =(B12-C12)

If you alter the original figures the anomaly usually disappears.
If you enter the same figures and formulas in a new spreadsheet the same thing happens.
I have Excel 2013 but I have tried the same thing in 2007 and 2016 versions with same result
See attachment for clearer explanation

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

You've left out some of the formulas, how do you get 3.5 ? Or 1.4 ?
Post ALL the formulas you're using, not just some of them

Out of time

No file attached!!!

Did you enter those numbers, or are they the results of other formulae/imported from another file?

Originally Posted by Mulpuzzle

TheN: I don't know why you get correct values and I don't think your suggested answers are correct. Here's my spreadsheet.

WHERE is your spreadsheet? You still haven't attached it!

I did. No issues here...

Originally Posted by Mulpuzzle

Glenn Kennedy: You've got the figures wrong. The right hand column should be 0.7 in each of the 5 rows. See attached.

Malpuzzle - you still have not attached your copy of the file. That is why people are having to recreate the data for you, and that is not necessary. Please do as I asked in post #2.

Hi Gleen

@Mulpuzzle is right.
Using your file 'Numbers.xls' change A4 to zero end B5 to 0.7 and see de difference.

Here are the instructions again:

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

DON'T try to use the paper clip - it doesn't work.

Originally Posted by José Augusto

Hi
I can resume the problem
=A6-B6
=(A6)-B6
=A6-(B6)
=(A6-B6)
The first three formulas give zero but the latter does not.

OK, so can you provide a sample file then, please?

Thanks. I can replicate the problem here, too.

=(B6-A6) results in the same number, but positive.

Originally Posted by Mulpuzzle

So now that some of you have confirmed this peculiar anomaly where do we go from here? Is it time to tell Microsoft "sorry guys there's a fault in Excel. It's integrity is fatally undermined."?

Yep - looks like it! What do you think we should do? I mean, there is nothing wrong per se with the formulae you are using, so use one that currently gives the answers you need.

Do a Google search on Jerry Lewis Excel floating point errors.

No, this is not an excel error, it has to do with how all computers handle the floating point situation. If you google it, you will get a bunch of hits, here is the text from just 1 of tghem...

The most common situation is illustrated by the decimal number 0.1. Although it has a finite decimal representation, in binary it has an infinite repeating representation. Thus when = 2, the number 0.1 lies strictly between two floating-point numbers and is exactly representable by neither of them.

Originally Posted by Mulpuzzle

it seems amazing that this should be necessary.

I agree but that's the reality of the situation.

Is there a simpler way?

Rounding is the best option.

As Tony pointed out, the best way around this is to use rounding, just select the number of decimals you are willing to work with