Excel Bug?

[ccurtis]

The following two Excel formulas give me different result, even though they are mathematically identical and follow the rules for order of operation/precedence properly. (Entered here exactly as in Excel)

=0.6 + 0.4 * EXP(-((A4 - 3000)/2000)^2)

=0.6 + 0.4 * EXP(-((A4 - 3000)^2 / (2000)^2))

Any ideas? Can someone duplicate my experience? A4 has 2000 entered, but the only time they give identical result is with 3000 entered.

 

[Nigel Goodwin]

As I see it, the formulas are clearly completely different, the second has an extra '^2' applied to (A4-3000), the first one doesn't. There's also a considerable amount of controversy about rules of precedence, best to use brackets to remove any such concerns.

But mostly, is the extra '^2' needed?, if so use that version, but they certainly aren't identical.

 

[ccurtis]

When you consider that (a/b)^2 is equal to (a^2)/(b^2), yes indeed, in one version, there is an unnecessary '^2' term. But, they are identities and should give the same result. For some unknown reason, it turns out it's the second expression only, in my original post, with the extra '^2' term, that gives the correct result in Excel. As far as I can tell, the first expression should too.

 

[Ian Rogers]

They don't look the same...

 

[Diver300]

If you put the exponent terms into Excel:-
-((A4 - 3000)/2000)^2
-((A4 - 3000)^2 / (2000)^2)

the first is always positive and the second is always negative.

It's the precedence of the minus sign and ^2 that is ambiguous, or is Excel is getting wrong.
-((A4 - 3000)/2000)^2
in this expression, Excel is applying the minus sign to the section in bold brackets, and then squaring it.

if you use
-(((A4 - 3000)/2000)^2)

you get the same as the second expression.

 

[Nigel Goodwin]

As I've already said, the formulas are completely different, I'm quite bemused why you would even try different versions?. The reason they both work with 3000 is that the result of the initial subtraction is 0, so the first part doesn't do anything. Computer maths tends to be non-integer, so instead of the subtraction result being -1000 (2000-3000), it may be -999.99999 or -1000.1 or something similar, resulting in final differences.

 

[ccurtis]

Thanks guys. Nigel, only because the first expression doesn't give the expected answer did I try the second expression in the first place. The first one should have worked, but doesn't. Especially thanks to Diver300 for that discovery. I'm thinking application of the minus sign in the first expression, first before squaring, is the stated order of precedence and I just didn't realize that, because that's counterintuitive to me since the minus sign is outside the parenthesis.

 

[Nigel Goodwin]

I never rely on precedence, as it's often controversial, so I always add brackets to ensure it does the maths the way I want This is mainly in programming languages, as I rarely use Excel.

 

[Diver300]

Logical operators are one place in particular where I'm careful about adding brackets.

If you don't have brackets, it's quite possible for new versions of the program or programming language to cause things to stop working.

In this case, the fact that you were using e raised to the power of something that is near zero hid the basic problem. It was not obvious that the sign had changed. If you had the two exponents side by side, it becomes really obvious that one is the negative of the other. If you had e raised to the power of a large number, and the sign of the exponent changed, the result would change from a huge number to a tiny one, which would also be obvious.