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")
This is a discussion on Fun formulas within the Lounge v.2.0 forums, part of the The Lounge category; Hi All. It's quiet here and I was wondering if anyone had any fun or interesting formulas that are not ...
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
A reply is not only helpful to others, but polite to those who have provided a solution!!
To post sample data go here....
HtmlMaker
Excel Genie
Use Borders
Upload a Sample File
Visit here for some EXCELlent videos...
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 by fairwinds; Dec 3rd, 2011 at 06:01 PM.
That's very good advice fairwinds . . .
The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !
Sub Macro()
ActiveCell = "IY" & Right(Application.Name, 5)
With ActiveCell.Characters(Start:=2, Length:=1).Font
.Name = "Webdings"
.Color = 255
End With
End Sub
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:
Take the following example: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
Excel 2010
A B C D E 1 Text never eat yellow snow 2 Hex Value (after substitutes) 1e2e3 ea4 5e6678 9178 3 Dec Value 123619 3748 6186616 37240 4 5 Replace With 6 n 1 7 v 2 8 r 3 9 t 4 10 y 5 11 l 6 12 o 7 13 w 8 14 s 9 Sheet1
Worksheet Formulas
Cell Formula B3 =HEX2DEC(B2)
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:
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.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")
Regards,
Jon von der Heyden
Forum Tools add-in for posting ranges
Posting guidelines | Forum rules | FAQs | Post code in code tags
Website: Excel Design Solutions | Twitter: @UnlockExcel
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
Howdy! Very tidy shg
Regards,
Jon von der Heyden
Forum Tools add-in for posting ranges
Posting guidelines | Forum rules | FAQs | Post code in code tags
Website: Excel Design Solutions | Twitter: @UnlockExcel
http://j-walkblog.com/index.php?/web...Quine_Formula/
The result of the formula is the formula itself.
.
Biff
Microsoft MVP - Excel
Using Excel 2002, 2007
KISS - Keep It Simple Stupid
Like this thread? Share it with others