Largest number?

[Lori]

What is the largest number that can be displayed in a cell?

Seems like a simple enough question. But I for one was surprised by the answer - thanks to a Stackoverflow post I found recently.

 

[GraH - Guido]

999999999999999 (15 nines) perhaps, due to FP15 preciseness?

 

[Lori]

That's the largest number that can be displayed in a cell that doesn't end with a zero.

And most people know the largest number that can be entered as a constant is:
9.99999999999999E+307

I was looking for a way to return values greater than this.

 

[Marc L]

999999999999999 (15 nines) perhaps, due to FP15 preciseness?

Right ‼ As written in Excel inner help ! …
​

 

[shrivallabha]

Interesting question.

A]
When I inserted:

9.99999999999999E+307

Displayed text became:

1.00E+308

And it was right aligned by default (excel treated it as a number).

But when I manually typed displayed text i.e.

1.00E+308

in another cell then the entry became left aligned (excel treated it as text)

B]
No math operation was possible on entries shown as text.

9.99999999999999E+307 allowed all math operation except multiplications. Even multiplications up to 1.797 were allowed.

9.99999999999999E+307*1.797 returned

1.80E+308

So Excel is showing some strange phenomenon at the specification limit as it can then be argued that the largest possible number that can displayed in Excel is in the range of 1.80E+308.

I have Excel 2010 in office where I have tested this. I have Excel 2016 at home which I will test in the evening.

Results on Excel 2016 are the same. Just out of curiosity, accuracy of multiplier is checked up to 1.7976931348.

 

[vletm]

Lori - Excel specifications and limits - link below
https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
>> Calculation specifications and limits <<

 

[Lori]

@vletm: indeed that lists the largest standard number as a little greater than what shriva gave.

However, the double precision format also allows for some other special values. One way to return the '+Infinity' value from VBA is:

Function INF() as Double
On Error Resume Next
INF = 1 / 0
End Function

Entering in a cell: =INF() results in a number equal to 2^1024 which is just greater than the value in that link.

This value can also be used in some formulas such as: =LOOKUP(INF(), A:A) to guarantee the last number in a list is returned (though arithmetic operations result in an error.)

To 2dp this displays as 1.80E+308 so maybe Shriva was right?

 

[vletm]

Lori
Which one would know better
... Microsoft itself or someone else?

 

[GraH - Guido]

Regarding floating point precision: add-ons are available to work with larger numbers or more precise numbers.
http://www.excel-ticker.com/calculation-of-very-large-numbers-in-excel-part-5-add-in/
http://tukhi.com/tukhi_fun.html
and perhaps some more...

 

[Lori]

@vletm - that link is correct but the question was a slightly trick one as it asked for the displayed number and was just asked out of curiosity.

My answer is 3.6E+308 which is the display value of NaN on the sheet. Make of it what you will...

(This value also has a use in charting as it results in a gap which otherwise is not possible with a formula result.)

 

[Chihiro]

Which one would know better
... Microsoft itself or someone else?

Acutally... MS documentation has a lot of mistakes, especially in non-English versions.

At any rate, maximum value a double type can accurately hold/display is.

1.79769313486231E+308

Not.

1.7976931348623158e+308

There is loss of precision with latter.

You can test by putting latter into double type in VBE.

It will return as...

 1.79769313486232E+308

since Excel can hold 15 significant digits.

But if you try make double variable (x) = that return value (by typing or other method), it's going to cause overflow.

 

[vletm]

Lori - but the question was a slightly trick one as it asked for the displayed number and was just asked out of curiosity.
If You write about term 'Largest Number'
... for me, it means 'Largest Number' nothing else!
So, take care...

 

[Lori]

Ok, I admit the question was a bit ambiguous. For me, all answers posted so far have some validity.

Chihiro rightly points out limitations of using 15 significant figures as the result displayed is larger than the max value which needs 17 figures to enter as in the link of vletm. I think you can use this formula to get the same result entered in A1, say:

=2^1023*(2-2^-52)

Special double values like infinity and nan could be argued to be larger, eg:
=INF()>A1
=ISNUMBER(INF())
both return TRUE but then such values do not have a finite value, similar to cardinal / ordinal distinction in maths.

Using an addin (xNumbers perhaps?) as per GraH Guido 's suggestion you might be able to use:

=REPT(9,32767)

Or an even larger number using a text floating point representation which would be much larger than those above.

The point of questions like this is really just to raise an interesting and lively discussion as well as hopefully learn something new!

 

[Khalid NGO]

The point of questions like this is really just to raise an interesting and lively discussion as well as hopefully learn something new!

Hi Lori,
That's we always do whenever see your interesting and insightful post.

Thanks for sharing.

 

[shrivallabha]

Just curious, are these results the same on a Mac or behavior is different?

 

[Chihiro]

I'd imagine it's same, since data type is independent of OS.

 

[Lori]

Yes, numeric limits should be same - though last decimal place is rounded to 7 in langage references. Can see this from immediate window:

[A1]=1.7976931348623158e+308
?[A1].Value(11)

(Others might know already but .value(11) is new to me in showing the extra digits in cell values)

Another way is to use a byte array (https://stackoverflow.com/a/896292) with same results:

=GetIEEE754SpecialValue(4)

And setting all bytes in the function to 255 displays as twice this value (3.60E+308).