Fun formulas

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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")
 
Last edited:
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," ")
 
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:
Code:
=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.
 
Thanks for taking the time to explain this. I haven't grasped it fully yet but I'm working on it.
 
How about VBA?

Code:
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
 

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top