Fun formulas

[Akashwani]

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]

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]

That's very good advice fairwinds . . .

 

[TinaP]

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]

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

 

[Jon von der Heyden]

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]

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

 

[shg]

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]

Howdy! Very tidy shg

 

[T. Valko]

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

The result of the formula is the formula itself.