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

L

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.
 
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.
 
Interesting question.

A]
When I inserted:
9.99999999999999E+307
Click to expand...
Displayed text became:
1.00E+308
Click to expand...
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
Click to expand...
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
Click to expand...

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:
@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 - 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

  • SpecialNum.xlsm
    23.3 KB · Views: 0
Which one would know better
... Microsoft itself or someone else?
Click to expand...

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.
 
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...
 
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!
 
Lori said:
The point of questions like this is really just to raise an interesting and lively discussion as well as hopefully learn something new!
Click to expand...

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

Thanks for sharing.
 
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).
 
You must log in or register to reply here.
Back
Top