9.9999999 .... ?

[Pekkavee]

Hey there

I see this

9.99999999999..

to be used now and then in formulas like
=IF((Sheet2!D2<>"")*(Sheet2!D2=$A$1),LOOKUP(9.99999999999999E+307,$B$1:B1)+1,"")

What is the function of that. I know it works in those cases I have seen but I would like to learn how to use it and why?

Regards
Pekka

 

[Pekkavee]

Thanxs everybody

Pekka

 

[mgirvin]

Yes, now in 2008 after the latest Challenge, This is a vary valuable post to read!

Thanks!

 

[jevi]

So interesting . Now i know smth more . Thank you Aladin.

Apparently, I shouldn't have used positive as qualifier... What follows is from the Help file under the heading: Excel specifications and limits

Feature Maximum limit
Number precision 15 digits
Largest number allowed to be typed into a cell 9.99999999999999E+307
Largest allowed positive number 1.79769313486231E+308
Smallest allowed negative number -2.2250738585072E-308
Smallest allowed positive number 2.229E-308
Largest allowed negative number -1E-307
Length of formula contents 1,024 characters
Iterations 32,767
Worksheet arrays Limited by available memory. Also, arrays cannot refer to entire columns. For example, an array cannot refer to the entire column C:C or to the range C1:C65536. However, an array can refer to the range C1:D65535 because the range is one row short of the maximum worksheet size and does not include the entire C or D column.
Selected ranges 2,048
Arguments in a function 30
Nested levels of functions 7
Number of available worksheet functions 329
Earliest date allowed for calculation January 1, 1900 (January 1, 1904, if 1904 date system is used)
Latest date allowed for calculation December 31, 9999
Largest amount of time that can be entered

 

[Aladin Akyurek]

So interesting . Now i know smth more . Thank you Aladin.

You are welcome. Thanks for providing feedback.

 

[Rick Rothstein]

Largest number allowed to be typed into a cell 9.99999999999999E+307

Maybe in place of using 9.99999999999999E+307 for our largest number in LOOKUP's, we could use this 7-character shorter number instead... 10*1E+307

 

[mfexcel]

Just to remind there is new limits for Excel 2010

Feature Maximum limit
Number precision 15 digits
Largest number allowed to be typed into a cell 9.99999999999999E+307
Largest allowed positive number 1.79769313486231E+308
Smallest allowed negative number -2.2250738585072E-308
Smallest allowed positive number 2.229E-308
Largest allowed negative number -1E-307
Length of formula contents 1,024 characters
Iterations 32,767
Worksheet arrays Limited by available memory. Also, arrays cannot refer to entire columns. For example, an array cannot refer to the entire column C:C or to the range C1:C65536. However, an array can refer to the range C1:D65535 because the range is one row short of the maximum worksheet size and does not include the entire C or D column.
Selected ranges 2,048
Arguments in a function 30
Nested levels of functions 7
Number of available worksheet functions 329
Earliest date allowed for calculation January 1, 1900 (January 1, 1904, if 1904 date system is used)
Latest date allowed for calculation December 31, 9999
Largest amount of time that can be entered