excel formula calculations are wrong

[Guest]

typical:
Cell A1 I have 0.2
Cell B1 I have 0.22
Cell C1 I have formula : +(B1-A1), and the answer is 0.02, correct
However, in Cell D1, I have a formula: +Power(c1,2), and the answer is
0.0005, WRONG,
The correct answer is of course 0.0004, but all simailar calculations are in
error.
I even put in the formula for D1 as +(c1*c1) and I still get the same WRONG
answer. Please advise

 

[Ian]

Could this be a rounding error? Are you values in A1 & B1 exact, or are they
rounded to 1 or 2 decimal places.

eg A1=0.20, B1=0.222. With A1 to C1 formatted to 2dp and D1 formatted to
4dp, you will see the problem you describe.

 

[Guest]

I cannot reproduce your results. Format A1:B1 to show 15 decimal places. If
you still think Excel has made an error, then post the full precision values.

Jerry

 

[Ron Rosenfeld]

typical:
Cell A1 I have 0.2
Cell B1 I have 0.22
Cell C1 I have formula : +(B1-A1), and the answer is 0.02, correct
However, in Cell D1, I have a formula: +Power(c1,2), and the answer is
0.0005, WRONG,
The correct answer is of course 0.0004, but all simailar calculations are in
error.
I even put in the formula for D1 as +(c1*c1) and I still get the same WRONG
answer. Please advise

Most likely, you don't really have 0.2 in A1 and/or 0.22 in B1.

Are these values the results of formulas? If so, then you are seeing a rounded
result, which does not truly reflect the contents of those cells.

Reformat those cells as Number with 15 decimal places, and see what is really
there.
--ron

 

[Guest]

The numbers I put in are exact. I, me, personally, put in those digits. I
actually went on google, put in my complaint and searched. I got several
hits, and they told me to go to "tools" and change the precision. It
corrected the problem.