35

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
k88074
  • 2,042
  • 5
  • 29
  • 43

4 Answers4

36

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.

  • 1
    And if for whatever reason you need to use that specifically large a number, you can always declare a constant equal to it with your desired reference name. – Grade 'Eh' Bacon Oct 15 '15 at 14:15
  • 2
    Excel's formal approach to stating infinity is showing #NUM! in the cell. Mathematically, you can use `=-1*LOG10(0)` formula that yields infinity (see [Wolfram|Alpha](https://www.wolframalpha.com/input/?i=-log(0))). – Gürol Canbek Feb 23 '17 at 07:25
  • 1
    @Gurol'Ca Can `=-1*LOG10(0)` formula be used to compare numbers to? If not I cannot really consider it infinity in Excel, no matter what Wolfram Alpha, or other mathematicians says. – Adam L. S. May 01 '17 at 13:16
  • @AdamL.S. you are right. I don't know why but Excel does not distinguish infinity and allow comparison and other valid calculations on infinity like for instance R. As an example if `A1 = -1*LOG10(0)` (so `#NUM`) `A1 > 1` yields `#NUM` in Excel, but `Inf > 1` yields `TRUE` in R. – Gürol Canbek May 02 '17 at 19:13
  • @Gurol'Ca - Depending on the mathematician you speak to, the #DIV/0! error could be considered infinity since there are an infinite number of zeroes in any integer. –  May 02 '17 at 19:27
  • I tried to compare the largest number excel can represent to the infinity symbol, and it was considered greater. Then I noticed that any data, that cannot be represented/converted as a number considered to be greater than any number. I couldn't find any value, that is equivalent with minus infinity. – Adam L. S. May 03 '17 at 21:44
  • @Jeeped division by zero is not infinite but undefined. Take an example: 44/0 is actually equal to find 'what number times zero gives 44?' but zero times any real number is zero not 44 because zero is absorbing element. – Gürol Canbek May 03 '17 at 21:46
  • @Gurol'Ca, By your logic, `=0/0` should not be a `#DIV/0!` error since **any** number times zero will equal zero. Again, it depends on which mathematician you talk to; It's like asking a roomful of mathematicians whether -1 or -2 is greater; the whole community seems split on which one is *greater* instead of *further from zero*. Google these two subjects sometime; you'll be surprized at the varying opinions. ([this](https://www.google.ca/search?q=is+divide+by+zero+infinite&rlz=1C1CHZL_enCA733CA733&oq=is+divide+by+zero+infinite&aqs=chrome..69i57j0l3.12567j0j7&sourceid=chrome&ie=UTF-8)) –  May 03 '17 at 23:22
6

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(,))
zx8754
  • 52,746
  • 12
  • 114
  • 209
lori_m
  • 5,487
  • 1
  • 18
  • 29
0

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

Antonello
  • 6,092
  • 3
  • 31
  • 56
0

Similar to what someone else said, I'd recommend just using large numbers instead, because for most uses it'd be functionally equivalent.

9e99 is good if you just need something fast.

Because I felt pedantic I went and figured out how far I could push it, and I got =1.79769313486231E+308 as the largest number it would display before erroring.

If I change the 1 at the end to a 2, it errors, and if I add a 9, it trims it regardless of number formatting.

The_furf_of_July
  • 1