Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    The road to infinity

    Is it possible to represent the symbol for infinty as result of a calculation? I want someting like this:
    IFERROR (some calculation, "Symbol for infinity")
    The following article seems to suggest that it can be done: Insert ASCII or Unicode Latin-based symbols and characters
    But whatever combination of ALT + X I tried, I couldnt get it into the formula.
    The symbol itself is represented by the Unicode character 221E

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    See attachment.

    Of course this will display for ANY error in A1, but you may be able to work around that.
    Attached Files Attached Files
    Last edited by MartinM; 2015-02-08 at 06:13.

  3. #3
    Super Moderator BATcher's Avatar
    Join Date
    Feb 2008
    Location
    A cultural area in SW England
    Posts
    3,420
    Thanks
    33
    Thanked 195 Times in 175 Posts
    Why don't you try Insert -> Symbol? The infinity symbol is near the bottom of the list in "Mathematical Symbols", in all of the fonts I tried: "normal text", Arial and Consolas.
    BATcher

    Time prevents everything happening all at once...

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    rvWoody,

    Converting Martin's formula to a User Defined Function (UDF), you can get past the "ANY ERROR" and isolate only the #DIV/0! error.

    Infinity.png

    In cell B1 enter the formula =Infinity(A1)

    Place this code in a standard module:
    Code:
    Public Function Infinity(rng As Range)
    Application.Volatile
        If IsError(rng.Value) Then
            If rng.Value = CVErr(xlErrDiv0) Then
                Infinity = ChrW(8734)
            End If
        Else:
            Infinity = rng.Value
        End If
    End Function
    This will have the same effect as Martin's formula but will not change to "∞" on any error

    OR, you could use the following formula instead in B1 =IF(ERROR.TYPE(A1)=2,"∞")

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2015-02-08 at 12:45.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    rvWoody,

    My overall interpretation of your request is that you do not wish to evaluate the result of one cell's formula by another, rather you want an infinity symbol to be the result of a formula producing a #DIV/0! error.

    Placing the following code into a worksheet module will accomplish this. If the formula evaluates to a #DIV/0! error than an infinity symbol will take its place. Even though you will end up with the desired result, the offending formula will be overwritten by the symbol. The code will look for the error anywhere on the sheet and will work on the cell when you enter the formula or if you change a precedent cell which is the denominator to zero.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
        If IsError(Target.Value) Then
            If Target.Value = CVErr(xlErrDiv0) Then
                Target.Value = ChrW(8734)
            End If
        End If
        If Target.Dependents.Value = CVErr(xlErrDiv0) Then
                Target.Dependents.Value = ChrW(8734)
        End If
    End Sub
    HTH,
    Maud

  6. #6
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    Thanks to all for replying so quickly.

    The most straightforword solution would be the use of Maudibe's suggestion: =IF(ERROR.TYPE(A1)=2,"∞"), but if I try to insert the ∞ symbol (from the math symbol list as suggested by BATcher) in the formula bar, I can't access it, as the ribbon is grayed out. That's where the original problem started.
    I'm aware that the IFERROR function catches any error, but in this particular case only a #DIV/0 could occur (may be I'm naive).

    So I have to stick (albeit reluctantly,because of the .xlsm and it security settings implications) with the UDF (MartimM, Maudibe). But I can live with that. Thanks again.

    BTW: has anyone understood an tried the ALT+X suggestion in the link I provided?

    BTW2: It is also my understanding that I now insert a Text symbol into a numerical table and that without some convolutions no further calculations can be done on that table. So I have to limit the use of the ∞ symbol to the final result for a better visual representation. Possibly an arithmetic with infinities should be introduced in EXCEL in a similar way as NULLs are treated in data bases. Wishful thinking, I suppose.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    rvWoody,

    Just copy the formula from above and paste into the formula bar in Excel. You will retain the infinity symbol

    rvWoody.png

    If you do use vba, the code in post #5 works the best and is the most versatile.

    Maud
    Attached Files Attached Files

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    rvWoody (2015-02-09)

  9. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    There's another angle to this, which may help you in this specific instance and more generally.

    As far as I know (I don't have copies of all versions of Excel), you can never insert a symbol from the Insert menu when typing a formula.

    But you can get it into a formula fairly easily:

    1. Click on a blank cell and Insert > Symbol > choose the ∞ symbol
    2. Click in the formula bar, select the symbol and copy it with CTRL C
    3. Whilst you are writing the formula in which you want to include the symbol, click CTRL V and the ∞ will appear at the insertion point.

    HTH

  10. The Following User Says Thank You to MartinM For This Useful Post:

    rvWoody (2015-02-09)

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Martin

    ..you are correct.
    When I want to use a symbol in a formula, I insert the symbol first, then add the formula 'around it' in the formula bar. But if I want to use it more than once in a formula, your CTRL V method is better.
    Or, stick the symbol into a cell and then name that cell. You can then use the name in a formula. (I use named cells like greenTick and redCross)

    So rvWoody could then use something like:
    IFERROR (some calculation, infinity)
    ..where infinity is that named cell

    zeddy

  12. The Following User Says Thank You to zeddy For This Useful Post:

    MartinM (2015-02-09)

  13. #10
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    Thanks again to all: copying a reference to a spreadsheet cell that contains the symbol and constructing the formula around it in the formula bar does the job.
    Last edited by rvWoody; 2015-02-10 at 02:20. Reason: untested first answer

  14. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    ..you mean like naming the cell that contains the symbol, for example, assigning the name infinity to that cell, and then using the named cell in a formula, like IFERROR (some calculation, infinity)????
    This makes it easier to read than something like IFERROR (some calculation, cell reference)
    (To quickly name a cell, put the cellpointer on that cell, and then in the 'name box' (just to the left of the formula bar) just type the name you want.)

    zeddy

  15. #12
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    zeddy, I do consider a named range as a reference. I do agree that the use of a name is less error prone.

  16. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Of course, you don't need to put it in a cell to name it. You can just define a name Infinity that refers to ="∞"
    Regards,
    Rory

    Microsoft MVP - Excel

  17. The Following User Says Thank You to rory For This Useful Post:

    zeddy (2015-02-11)

  18. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Rory

    That's a good reminder.
    But I would still use infinity rather than Infinity, otherwise it might look like a built-in constant.

    zeddy

  19. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Excel doesn't have built-in constants, other than in VBA.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •