Results 1 to 15 of 20
Thread: The road to infinity

20150208, 04:38 #1
 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 Latinbased 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

20150208, 06:10 #2
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 1,121
 Thanks
 79
 Thanked 129 Times in 113 Posts
See attachment.
Of course this will display ∞ for ANY error in A1, but you may be able to work around that.Last edited by MartinM; 20150208 at 06:13.

20150208, 06:10 #3
 Join Date
 Feb 2008
 Location
 A cultural area in SW England
 Posts
 3,564
 Thanks
 36
 Thanked 212 Times in 189 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
Mission: identify what we do best and find more ways of doing less of it better

20150208, 12:34 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 3,038
 Thanks
 166
 Thanked 800 Times in 729 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
OR, you could use the following formula instead in B1 =IF(ERROR.TYPE(A1)=2,"∞")
HTH,
MaudLast edited by Maudibe; 20150208 at 12:45.

20150208, 22:00 #5
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 3,038
 Thanks
 166
 Thanked 800 Times in 729 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
Maud

20150209, 03:22 #6
 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.

20150209, 05:02 #7
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 3,038
 Thanks
 166
 Thanked 800 Times in 729 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

The Following User Says Thank You to Maudibe For This Useful Post:
rvWoody (20150209)

20150209, 06:40 #8
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 1,121
 Thanks
 79
 Thanked 129 Times in 113 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

The Following User Says Thank You to MartinM For This Useful Post:
rvWoody (20150209)

20150209, 11:21 #9
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,442
 Thanks
 166
 Thanked 651 Times in 619 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

The Following User Says Thank You to zeddy For This Useful Post:
MartinM (20150209)

20150209, 12:39 #10
 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; 20150210 at 02:20. Reason: untested first answer

20150210, 04:59 #11
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,442
 Thanks
 166
 Thanked 651 Times in 619 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

20150211, 11:44 #12
 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.

20150211, 11:52 #13
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,353
 Thanks
 4
 Thanked 229 Times in 210 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

The Following User Says Thank You to rory For This Useful Post:
zeddy (20150211)

20150211, 12:10 #14
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,442
 Thanks
 166
 Thanked 651 Times in 619 Posts
Hi Rory
That's a good reminder.
But I would still use infinity rather than Infinity, otherwise it might look like a builtin constant.
zeddy

20150212, 04:52 #15
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,353
 Thanks
 4
 Thanked 229 Times in 210 Posts
Excel doesn't have builtin constants, other than in VBA.
Regards,
Rory
Microsoft MVP  Excel