Excel calculating wrong answer

[TommySmores]

I have been working on a Spreadsheet to calculate the Poisson Probability Distribution for my Stats class, and i've run into an issue when trying to use Euler's Constant in the first part of the equation.

The equation is as follows: P(x)=(e^-λ*λ^x)/4! , where e= ~2.7183. My trouble comes when trying to calculate e^-λ. In Excel I have it as...

=E5^-B3

Where E5 = e, and B3 is my mean, or λ=1 in my case, so the equation is 2.7183^-1. When plugged into excel i receive 0.35464, but when done by hand and using my calculator, I get 0.36787.

Calculations are set to Automatic. I thought it may be a rounding issue but every fix for that hasn't yielded the right answer.

[#2]

After stumbling across another post from someone with a similar issue, I tried the same exact formula but with the numbers instead of referencing the cells and it gave me the correct answer, any insight as to why this is would be very helpful!

[#3]

@TommySmores 

First, the Excel expression for e^(-1) should be EXP(-1) or EXP(-B3), where B3=1.

That displays 0.367879441171442, which truncates (!) to 0.36787.

Second, you should not use or show us rounded (much less truncated) numbers, if you want the most accurate result, especially if your calculator has a button labeled "e" or "exp".

In Excel, "e" is EXP(1), which displays 2.71828182845905.  (It is exactly 2.71828182845905 - 4.88E-15.  But most people do not know how to see the residual value 4.88E-15.

Although that does round to 2.7183, note that 2.7183^(-1) or more simply 1/2.7183 displays 0.36787698193724, which is less than EXP(-1).

Finally, I doubt that there is any significant difference if you use cell references, unless the actual cell values are different to 15 significant digits, like the rounded and truncated values that you posted.

However, 0.35464 is the rounded or truncated result of a value between 1/2.81968137600452 and 1/2.81980064009475.  That does not include EXP(-1), which again is 1/2.71828182845905.

So the likely explanation for your seeing 0.35464 is:  it is the result of a typo or some other mistake.