math accuracy

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

0.02778
2.8 this cell = 100X cell above

1.0
1.138 this cell = power(cell above- cell 2above, 2)/(cell 2 above)

should be (-1.8^2)/2.8=1.157

version excel 2002 (10.4521.4219)SP2

this is very worrying,

I also did ((B8-B6)*(B8-B6))/B6 gave same wrong answer

Any ideas ??
 
Hugh

You have a huge amount of rounding going on in your theory that Excel just
isn't doing.

0.02778*100 doesn't make 2.8. It makes 2.778.

Take 1-2.778 and you have -1.778 not 1.8.

Raise that to the power of two and you have 3.161284

Divide that by 2 and you have 1.580642

The above answer is correct given your input. Where confusion may occur is
in what Excel is displaying. Formatting may show 0.02778*100 to equal 2.8
but in fact XL is storing and using in ongoing calculations the true figure.
(Try expanding the number of decimals shown and you will see the figure
change).

Excel holds this accuracy up to 16 decimals places.

If you truly want to 'trim' the result to one decimal then you would need to
use the ROUND function.

=((1-ROUND(0.02778*100,1))^2)/ROUND(0.02778*100,1)

=1.157142857

Obviously you can switch the constants used above for cell references but be
careful that what you see is what you actually have.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Back
Top