Can Somebody Do An Excel Thing For Me?

[Doofus]

In a blank worksheet, enter the following:

A1:23022.51
A2:0
A3:3448.44
A4:0
A5:26470.95
A6:0

Then do a formula

+A1-A2+A3-A4-A5+A6

The answer should be zero, but if you format the answer to, say 15 decimals, the answer isn't zero.

Why?

If I change A5 to =A1+A3, then the answer is zero, even though the numbers are the same.

If I change A1, A3 and A5 to, say, 81962.23, 13401.98 & 95364.20, then the answer is zero.

WTF?

[evenflow]

I have no idea, but I've just done it and that IS weird

[LeoSayer]

Same here.

No explanation other than it must be a bug. I'd like to know the cause.

[evenflow]

If you take out the gaps in cell A2 and A4 so you end up with the formula =A1+A2-A3 it comes out as 0.00000000000 ?!

[LeoSayer]

Might be this:
https://www.microsoft.com/en-us/microsoft-365/blog...

If you apply a number format the you get a 0.00.

[paulrockliffe]

Something in here:

https://www.microsoft.com/en-us/microsoft-365/blog...

Not sure why your formula only fails when you reference the zero cells. It also fails when these are blank too.

[Keypad]

If you select all the cells (a1 - a7) and format them as "Number", rather than leaving them as the default of "General", a7 gives the correct answer of zero.

I think that by using the default of General it's somehow using scientific notation. No idea why, however.

[Mr Pointy]

I t seems to be the last +0 that causes the issue. If you evaluate each step they are exact to 2 places of decimals as is the formula of the first five steps which evaluates to exactly zero but that sixth step of +0 gives a non-zero value. Weird.

[Mr Pointy]

Keypad said:

If you select all the cells (a1 - a7) and format them as "Number", rather than leaving them as the default of "General", a7 gives the correct answer of zero.

I think that by using the default of General it's somehow using scientific notation. No idea why, however.

I don't think so - I tried it with cells formatted as numbers to 30 places & it still goes wrong.

[Doofus]

Keypad said:

If you select all the cells (a1 - a7) and format them as "Number", rather than leaving them as the default of "General", a7 gives the correct answer of zero.

I think that by using the default of General it's somehow using scientific notation. No idea why, however.

the issue arose because I tend to format numbers so that zero shows as a dash. But this number was showing as (0.00) instead of "-".

Incidentally, the same error (but with a differnet number as the result) occurs if you replace A1, A3 and A5 with (inter alia):

3238.66, 380.74, 3619.40
100644.67, 18233.19, 118877.86

But not:

7020.73, 2837.52, 9858.25
3483.96, 1675.38, 5159.24

It's not a showstopper, it's just bloody odd.

[Mr Pointy]

What gets me is that the answer to the first five steps is zero to 30 places but subtracting zero gives a wrong answer.

[Doofus]

Mr Pointy said:

What gets me is that the answer to the first five steps is zero to 30 places but subtracting zero gives a wrong answer.

I know. But only sometimes.

[robbieduncan]

The answer is IEEE 754 (or something similar/later): https://en.wikipedia.org/wiki/IEEE_754

Basically fractions are a sort of estimate and adding them together might not get the precise answer you expect

[mmm-five]

If you put parentheses around each of the +/- sums, then you get 0.000000000000

(A1-A2)+(A3-A4)-(A5+A6)