Is there Infinity in Spreadsheets?

Question

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.

Answer 1

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(,))

Answer 2

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.