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.