• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Largest number?

Lori

Active Member
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.
 

Lori

Active Member
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.
 

shrivallabha

Excel Ninja
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.
 
Last edited:

Lori

Active Member
@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?
 

Lori

Active Member
@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.)
 

Attachments

Chihiro

Excel Ninja
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.
Code:
1.79769313486231E+308
Not.
Code:
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...
Code:
 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

Excel Ninja
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

Active Member
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

Excel Ninja
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.
 

Lori

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

Code:
[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).
 
Top