Excel calculation gone wrong need help?

Question

Excel calculation gone wrong need help?
I work a Amgen and I am having touble using Excel sheets calculating the following order :

10 ^ -690 ( ten to the negative 690th power), I always get theresult as zero (0).
Can anyone tell me how to define these numbers so the calculations and result express the exponents , i know the numer is extremely small but that is how it goes for this calculation. hope you undestand my question english is not my 1st or seconnd language.



it just occured to me what if i take the logarithm and express is it that way then at the end go back?

Answer 1

Excel cannot represent number that are that small. The standard floating point representation used by computers was not made to accomodate such small numbers. Work in logarithmic domain and convert back at the end of the computation.

Answer 2

You may have exceeded the precision of Excel - it probably can't represent a number that small. YOur two best bets are:

1. work in logs and express your final result as a decimal (base 10) number if it isn't so small.

2. Keep track of powers of ten yourself (if possible) so excel is only working with floating point numbers on the order of 10 in magnitude. Sort like we old guys did back in the slide rule days.

Answer 3

In Vbasic in EXCEL, Double (double-precision floating-point) variables are stored as IEEE 64-bit (8-byte) floating-point numbers ranging in value from -1.79769313486231E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values.

Since -609 is waaaaaay beyond the capabilities of the spreadsheet... you get 0.

One way to do it is to write a subroutine that handles the significant digits as double precision, and keeps track of the exponents separately. Then you could enter the significant digits into one cell and the exponent in another, and let the subroutine keep track of the results.

Answer 4

The smallest allowed positive number in Excel is
2.229E-308. Since your number is 1E-690 it will not be displayed.

The reciprocal of 1E-690 is probably way more than the number of elementary particles in the universe so the chance of ever needing to use this number is nonexistent.

Answer 5

Did you format the cell where the result appears to show the maximum amount of decimal places? If not, it will round it off to the nearest number, which is probably zero.

Answer 6

Excel does have a limit to what it can show.
Try formatting the cel as "Scientific", but my Excel can't show anything smaller than "1.00E-307"