# Fun formulas



## Akashwani (Dec 3, 2011)

Hi All.

It's quiet here and I was wondering if anyone had any fun or interesting formulas that are not based on any data within the spreadsheet...

=SUBSTITUTE(ADDRESS(1,COLUMN(H1),4),"1","")&SUBSTITUTE(ADDRESS(1,COLUMN(E1),4),"1","")&SUBSTITUTE(ADDRESS(1,COLUMN(L1),4),"1","")&SUBSTITUTE(ADDRESS(1,COLUMN(L1),4),"1","")&SUBSTITUTE(ADDRESS(1,COLUMN(O1),4),"1","")

Simple, but this is the type of formula I was thinking about.

Ak


----------



## fairwinds (Dec 3, 2011)

Hi,

Try:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DEC2HEX(2298433)&DEC2HEX(-404887622166)&DEC2HEX(324135473)&DEC2HEX(59774192177)&DEC2HEX(150653386305)&DEC2HEX(44052435364)&DEC2HEX(7429275375),1," "),2,"I"),3,"T"),4,"S"),5,"O")


----------



## Gerald Higgins (Dec 5, 2011)

That's very good advice fairwinds . . .


----------



## TinaP (Dec 5, 2011)

It's kind of forced, but try this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DEC2HEX(584167),"7","R1"),"8","N"),"9","V")&DEC2HEX(234)&CHAR(84)&" "&CHAR(80)&SUBSTITUTE("GUN","G","R")&DEC2HEX(14)&"S1"&SUBSTITUTE("WHERE","RE","N ")&CHAR(89)&CHAR(79)&CHAR(85)&CHAR(49)&SUBSTITUTE(DEC2HEX(2766),"C","R")&1&REPLACE(DEC2HEX(64237),3,0,"MISH"),1," ")


----------



## mccdaddy (Dec 8, 2011)

Fascinating. Any chance someone could explain one of those formulas?


----------



## Jon von der Heyden (Dec 9, 2011)

mccdaddy said:


> Fascinating. Any chance someone could explain one of those formulas?



There are plenty here that would do a better job explaining it but as noone has replied (yet) I'll have a crack.

Fairwinds is (very cleverly) exploiting hexadecimal notation to create words, since hexadecimal numbers are represented as strings containing # characters [0-9] and letters [a-f].

From wikipedia:


> In mathematics and computer science, hexadecimal (also base 16, or hex) is a positional numeral system with a radix, or base, of 16. It uses sixteen distinct symbols, most often the symbols 0–9 to represent values zero to nine, and A, B, C, D, E, F (or alternatively a–f) to represent values ten to fifteen. For example, the hexadecimal number 2AF3 is equal, in decimal, to (2 × 163) + (10 × 162) + (15 × 161) + (3 × 160), or 10,995



Take the following example:
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Text</td><td style=";">never</td><td style=";">eat</td><td style=";">yellow</td><td style=";">snow</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Hex Value (after substitutes)</td><td style=";">1e2e3</td><td style=";">ea4</td><td style=";">5e6678</td><td style="text-align: right;;">9178</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Dec Value</td><td style="text-align: right;;">123619</td><td style="text-align: right;;">3748</td><td style="text-align: right;;">6186616</td><td style="text-align: right;;">37240</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="background-color: #95B3D7;;">Replace</td><td style="background-color: #95B3D7;;">With</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style=";">n</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style=";">v</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style=";">r</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">t</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style=";">y</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style=";">l</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style=";">o</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style=";">w</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style=";">s</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=HEX2DEC(<font color="Blue">B2</font>)</td></tr></tbody></table></td></tr></table><br />

Have a look at the words in B1:E1.  At the moment these are just text values but we will want to represent these as valid hex values.  Since only letters [a-f] apply we need to substitute any letters greater than 'f' with a numeric value.  I've chosen to number the letters 1-9 in the order of appearance, the substitution visible in table B6:C14.

The substituted values are visible in B2:E2.  The value that lie beneath in row 3 are the decimal values, here using the HEX2DEC worksheet function to tell us which values apply.

Now in order to wrap this up into a single formula giving us the entire text string we need to:

1. Convert each dec value to its' hex value, and concatenate the result to the previous result

2. Perform the substitutions per what is seen in the substitution table above.

Hence we get:

```
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DEC2HEX(123619)&DEC2HEX(3748)&DEC2HEX(6186616)&DEC2HEX(37240),1,"N"),2,"V"),3,"R "),4,"T "),5,"Y"),6,"L"),7,"O"),8,"W "),9,"S")
```

Stick this in a cell, and then use the Evaluate Formula tool (on the Formulas tab) to step through and check what happens at each step.


----------



## mccdaddy (Dec 9, 2011)

Thanks for taking the time to explain this. I haven't grasped it fully yet but I'm working on it.


----------



## shg (Dec 10, 2011)

How about VBA?


```
Sub shg()
    Dim i As Long
    Dim s As String
    
    For i = 0 To 3
        s = s & Chr((-40 * i ^ 3 + 135 * i ^ 2 - 128 * i + 345) / 3)
    Next i
    
    For i = 0 To 3
        s = s & Chr((-146 * i ^ 3 + 543 * i ^ 2 - 481 * i + 690) / 6)
    Next i
    
    For i = 0 To 4
        s = s & Chr((95 * i ^ 4 - 682 * i ^ 3 + 1393 * i ^ 2 - 638 * i + 2496) / 24)
    Next i
    MsgBox s
End Sub
```


----------



## Jon von der Heyden (Dec 10, 2011)

Howdy!  Very tidy shg


----------



## T. Valko (Dec 10, 2011)

http://j-walkblog.com/index.php?/weblog/comments/An_Excel_Quine_Formula/

The result of the formula is the formula itself.


----------



## Dryver14 (Dec 11, 2011)

Jon,

thanks for that explaination, the good thing is ... it still looks like voodoo to me but I am gonna look at it often to try and grasp what is happening.

I just LURVE hanging around with all you smart people. some of it's got to rub off.

starting from.
i = whatever you want..... isn't that brilliant.
just think about it for a second.

many regards,
Kev


----------



## taurean (Dec 17, 2011)

In Thisworkbook module, try:

```
Private Sub Workbook_Open()
Application.Visible = False
End Sub
```


----------

