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

 

[Mark W.]

9.99999999999999E+307 is the largest numeric value that can be represented in Excel. When you search for this value using an approximate lookup (i.e., when MATCH's 3rd, optional argument is 1 or omitted) then the last value in the lookup array is returned. LOOKUP is always an approximate match. Quoting the LOOKUP Help topic...

If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

 

[Aladin Akyurek]

...

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?...

A) The algorithm, known as binary search (BS), finds the largest value that is less than or equal to the search value in a reference.

B) Lookup functions like:

MATCH(LookupValue,Reference,MatchType), with MatchTpe = 1,

VLOOKUP(LookupValue,Reference,1,MatchType), with MatchType = 1,

HLOOKUP(LookupValue,Reference,1,MatchType), with MatchType = 1,and

LOOKUP(LookupValue,Reference)

all invoke binary search and attempt to return a result, conditioned upon the fact that matching/binary search finds the largest value that is less than or equal to the lookup value.

C) 9.99999999999999E+307 is the largest positive number Excel knows (hereafter, referred to as BigNum).

D) BigNum is an improbable value to occur in references of interest to us.

Given A-D, whenever there is a need to retrieve the last numerical value from a reference [1] or to determine the position of the last numerical value in a reference [2], we can invoke for...

[1]

=LOOKUP(BigNum,Reference)

=INDEX(Reference,MATCH(BigNum,Reference))

=VLOOKUP(BigNum,Reference,1), and for...

[2]

=MATCH(BigNum,Reference)

With MatchType omitted, Excel defaults to 1 (or TRUE). LOOKUP() is designed, as it were, to operate under MatchType = 1.

Note that BigNum is preferred instead of MAX(Reference)+1, which would play an identical role in the foregoing formulas, for reasons of efficiency: not too loose processing cycles with MAX.

For more, search on this board and thru Google.

 

[RalphA]

Aladin wrote:

C) 9.99999999999999E+307 is the largest positive number Excel knows (hereafter, referred to as BigNum).

Why is this number known as "the largest positive number Excel knows"? I can write, for example, these larger and much easier to write numbers, among others:
=9^323, which Excel shows as 1.6609E+308
=5^441, which Excel shows as 1.3762E+308
=3^646, which Excel shows as 1.6609E+308 (this one, 3^646, is mathematically identical to the first one, 9^323).

Now, I have a sneaking suspicion that, at least without further magic, 1.6609E+308 just might be THE largest number. Am I wrong?

 

[Aladin Akyurek]

Aladin wrote:

C) 9.99999999999999E+307 is the largest positive number Excel knows (hereafter, referred to as BigNum).

Why is this number known as "the largest positive number Excel knows"? I can write, for example, these larger and much easier to write numbers, among others:
=9^323, which Excel shows as 1.6609E+308
=5^441, which Excel shows as 1.3762E+308
=3^646, which Excel shows as 1.6609E+308 (this one, 3^646, is mathematically identical to the first one, 9^323).

Now, I have a sneaking suspicion that, at least without further magic, 1.6609E+308 just might be THE largest number. Am I wrong?

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

 

[RalphA]

Ahhh! Thanks again, Aladin, for your magic! Now I can't help but see it! But, why even use the BigNum? It seems a little troublesome to write in a formula. Why not just use LargeNum, defined as 9E+307 ? Wouldn't that large a number be a totally adequate and practical size to use for the cases in point? Or is it that it is imperitive to use the largest possible number allowed?

 

[Aladin Akyurek]

Ahhh! Thanks again, Aladin, for your magic! Now I can't help but see it! But, why even use the BigNum? It seems a little troublesome to write in a formula. Why not just use LargeNum, defined as 9E+307 ? Wouldn't that large a number be a totally adequate and practical size to use for the cases in point? Or is it that it is imperitive to use the largest possible number allowed?

I often suggest to define BigNum thru Insert|Name|Define as referring to:

9.99999999999999E+307

I don't see why to abolish a de facto consensus. It's mystifying and confusing to see so many variations, just because one needs once a while to type this big number. When it was suggested to invoke this constant as such instead of ever changing arbitrary big constants at the original MrExcel Board, I stopped posting variations ever since.

 

[Zack Barresse]

... When it was suggested to invoke this constant as such instead of ever changing arbitrary big constants at the original MrExcel Board, I stopped posting variations ever since.

Although some of us didn't have the pleasure of being members of the original board - me included - it may be news to everyone.

 

[RalphA]

I accept! What you are saying is, I guess, that BigNum is now a de facto standard in MrExcel. Well, that sounds, to me, as reason enough to maintain it as defined, and explains, at least to me, why you don't use 9E+307.

Of course, folks do say, "Rules are meant to be broken", "This is the exception that proves the rule", and, "Nothing is set in concrete" (and, I have seen badly-poured concrete torn up and repoured...).

 

[tusharm]

For a different take on the 'wisdom' of using this 9.however-many-9s-E-whatever approach see http://groups.google.com/groups?threadm=MPG.1973429a8eb8e78e98aba5@msnews.microsoft.com

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