Is there Infinity in Spreadsheets?

I am wondering if there is any way to represent infinity (or a sufficiently high number) in MS Excel.

I am particularly looking for something like Double.POSITIVE_INFINITY or Double.MAX_VALUE in Java.

3 Answers

I like to use 1e99 as it gives the largest number with the fewest keystrokes but I believe the absolute maximum is actually 9.99999E+307. At that stage of the number spectrum I don't think there is much difference as far as Excel is concerned.

I think it's worth adding that, Infinity as well as other special values can be returned from a vba function (How do you get VB6 to initialize doubles with +infinity, -infinity and NaN?):

 Function Infinity(Optional Recalc) As Double On Error Resume Next Infinity = 1/0 End Function 

When entered as a cell formula a large number is shown (2^1024). You can set a conditional format to show "+Infinity" as a number format with a formula condition:

=AND(ISNUMBER(A1),A1>2^1023*(2-2^-52)) 

A dummy argument containing a dynamic reference can be inserted so that values are recalculated when the workbook is opened, for example:

=Infinity(IF(,) IF(,)) 

With LibreOffice 6 I use 1.79769313486231E+308 that seems the largest number it allows me to enter, but I miss not having an exact representation of +- infinite, also because I suspect the number above is implementation specific...

This is an other point that makes me think that spreadsheets are great for visualising, editing and simple computations on tabular data, but for doing more complex operations/modelling a real programming language is a must...