9.9999999 .... ?

Pekkavee

Well-known Member
Joined
May 25, 2004
Messages
1,218
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
:eek: :eek: :eek:
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.
 
Upvote 0
Pekkavee said:
...

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.
 
Upvote 0
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?
 
Upvote 0
RalphA said:
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
 
Upvote 0
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?
 
Upvote 0
RalphA said:
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.
 
Upvote 0
Aladin Akyurek said:
... 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.
 
Upvote 0
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...). :biggrin:
 
Upvote 0
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
Pekkavee said:
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
:eek: :eek: :eek:
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top