1. ## 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. See attachment.

Of course this will display for ANY error in A1, but you may be able to work around that.

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

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

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

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

rvWoody (2015-02-09)

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

14. 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. zeddy, I do consider a named range as a reference. I do agree that the use of a name is less error prone.

16. 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 ="∞"

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

zeddy (2015-02-11)

18. 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. Excel doesn't have built-in constants, other than in VBA.

Page 1 of 2 12 Last

#### Posting Permissions

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